I have a table with 3 fields. This table keeps record of call duration for each day to the relevant country for last 30 days.
C_Date Country C_Time
2012-10-01 India 1316
2012-10-01 USA 12
2012-10-01 UK 132
2012-10-01 Kuwait 134
2012-10-02 UK 135
2012-10-02 USA 136
2012-10-02 Singapore 137
I need to generate a table and a line graph using this records and I tried to write the MySQL query. Basically I need to draw this only for selected countries.
Say I want India, USA and UK ; then I need to pull a record set like this:
C_Date | Calling_Time
-----------+----------------------------------
2012-10-01 | 1316,12,132
2012-10-02 | 0,136,135
2012-10-03 | ...
... | ...
This is my Query:
SELECT C_Date, GROUP_CONCAT(C_Time
ORDER BY Country
SEPARATOR ',') as Calling_Time
FROM Call_Table t
WHERE Country
IN ('India', 'USA', 'UK')
GROUP BY C_Date
ORDER BY C_Date
The result was much closer, but it had ignored the empty results.
C_Date | Calling_Time
-----------+----------------------------------
2012-10-01 | 1316,12,132
2012-10-02 | 136,135 (expected 0,136,135)
Can I change my query to get the Calling_Time like 0,136,135 OR ,136,135 OR null,136,135 ?(with unique numbers of segments to represent each country )
Thanks in advance!
Try this query -
SELECT
C_Date,
CONCAT(MAX(IF(country = 'India', C_Time, 0)), ',',
MAX(IF(country = 'USA', C_Time, 0)), ',',
MAX(IF(country = 'UK', C_Time, 0))) Calling_Time
FROM call_table
GROUP BY C_Date
+------------+--------------+
| C_Date | Calling_Time |
+------------+--------------+
| 2012-10-01 | 1316,12,132 |
| 2012-10-02 | 0,136,135 |
+------------+--------------+