CODEWITHSUNDEEP
mysqlsql
Satish
Satish

Reputation: 17397

MySQL Query count with multiple group per date/time

I have following data in MySQL table:

+-------------------------------------+-----------------+
|            DATE                     | SipResponseCode |
+-------------------------------------+-----------------+
| 20 Feb                              |             200 |
| 20 Feb                              |             500 |
| 20 Feb                              |             200 |
| 20 Feb                              |             200 |
| 20 Feb                              |             487 |
| 20 Feb                              |             200 |
| 20 Feb                              |             200 |
| 20 Feb                              |             500 |
| 20 Feb                              |             500 |
| 20 Feb                              |             487 |
| 20 Feb                              |             200 |
| 20 Feb                              |             200 |
| 20 Feb                              |             200 |
| 20 Feb                              |             500 |
| 20 Feb                              |             200 |
| 20 Feb                              |             200 |
| 20 Feb                              |             200 |
| 20 Feb                              |             200 |
| 20 Feb                              |             200 |
| 20 Feb                              |             500 |
| 21 Feb                              |             200 |
| 21 Feb                              |             487 |
| 21 Feb                              |             200 |
| 21 Feb                              |             487 |
| 21 Feb                              |             487 |
| 21 Feb                              |             487 |
| 21 Feb                              |             487 |
| 21 Feb                              |             200 |
| 21 Feb                              |             200 |
| 21 Feb                              |             487 |
| 21 Feb                              |             487 |
| 21 Feb                              |             500 |

I want to write SQL query so it can give me count of SipResponseCode count per day 200, 487, 500

+-------------------------------------+------------+-----------+--------+
|            DATE                     |    200     |    487    |   500  |
+-------------------------------------+------------+--------------------+
| 20 Feb                              |     14     |      2    |    5   |
| 21 Feb                              |     4      |      7    |    1   |

I have tired but cannot get correct results.

Upvotes: 0

Views: 58

Answers (3)

Strawberry
Strawberry

Reputation: 33935

The standard answer would be roughly as follows:

SELECT date
     , sipresponsecode
     , COUNT(sipresponsecode) x 
  FROM my_table 
 GROUP 
    BY date
     , sipresponsecode;

Upvotes: 0

David Bélanger
David Bélanger

Reputation: 7438

The following query will give you the results, not like you want per say, but in an easy way to parse it later in your language (PHP, C#, etc.).

SELECT  DATE, GROUP_CONCAT(CAST(TEST AS CHAR(10000) CHARACTER SET utf8) SEPARATOR ",") AS myCol
FROM
(
    SELECT      DATE, CONCAT(SipResponseCode, "^",  COUNT(*)) AS TEST
    FROM        table1
    GROUP BY    DATE, SipResponseCode
) a
GROUP BY DATE

Upvotes: 0

AdamMc331
AdamMc331

Reputation: 16691

If the values 200, 487, and 500 are constant values that you are aware of at the time of writing the query, you can use those in a case statement of your select clause.

Something a little more readable than a case statement is using the SUM() function with a condition, which will essentially count the number of rows that meet that condition. Try this:

SELECT dateColumn, 
   SUM(SisResponseCode = 200) AS '200', 
   SUM(SisResponseCode = 487) AS '487', 
   SUM(SisResponseCode = 500) AS '500'
FROM myTable
GROUP BY dateColumn;

Upvotes: 1

Related Questions