Subquery with COUNT (mysql)

Question

I have the following table:

id   | user | createdAt           | pstatus  
-------------------------------------------------------
287  | Foo  | 2013-08-01 17:19:24 | PLAYING  
286  | Foo  | 2013-08-01 17:18:24 | IDLE  
285  | Foo  | 2013-08-01 17:17:24 | PLAYING  
284  | Foo  | 2013-08-01 17:16:24 | BUFFERING 
283  | Foo  | 2013-08-01 17:15:24 | STOPPED

How can I query this table to produce the following result? Essentially, I need to get the total number of different values of pstatus per user, per day.

User | logdate    | playCount | idleCount | bufferCount | StopCount
-------------------------------------------------------------------
Foo  | 2013-08-01 |         48|        62 |           0 |         4
Foo  | 2013-08-02 |         25|        22 |           0 |         0
Bar  | 2013-08-02 |         22|        32 |           5 |         4
Bar  | 2013-08-03 |         44|        11 |           1 |         0
Foo  | 2013-08-03 |         32|        03 |           0 |         0

I know I can use DATE(createdAt) to get the date per day instead of a timestamp, but I'm struggling with how essential merge the following into a single query:

COUNT(pstatus) AS playCount WHERE pstatus = "PLAYING";
COUNT(pstatus) AS idleCount WHERE pstatus = "IDLE";
COUNT(pstatus) AS bufferCount WHERE pstatus = "BUFFERING";
COUNT(pstatus) AS StopCount WHERE pstatus = "STOPPED";

I'm pretty sure it needs to be subquery, but struggling with the syntax!

M Khalid Junaid · Accepted Answer · 2014-04-13 13:20:21Z

3

You can use SUM with a condition this will result in a boolean and will give you the count based on conditions

SELECT
DATE(createdAt) logdate,    
SUM(pstatus = "PLAYING") playCount 
,SUM(pstatus = "IDLE") idleCount 
,SUM(pstatus = "BUFFERING") bufferCount 
,SUM(pstatus = "STOPPED") StopCount
FROM `table`
GROUP BY `User`,DATE(`createdAt`)

Demo

edited Apr 13, 2014 at 13:20

answered Apr 13, 2014 at 13:14

M Khalid Junaid

64.6k10 gold badges93 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Justin Mitchell Over a year ago

pstatus is on a second table

M Khalid Junaid Over a year ago

@JustinMitchell i don't see second table in OP's question

Neokoenig Over a year ago

Perfect - this was just what I was looking for. FYI final query looks like

SELECT     User,     DATE(createdAt) AS createdAtDate,      SUM(pstatus = "PLAYING") AS playCount     ,SUM(pstatus = "IDLE") AS idleCount     ,SUM(pstatus = "BUFFERING") AS bufferCount     ,SUM(pstatus = "STOPPED") AS StopCount     FROM pings     GROUP BY User,DAY(createdAt)     ORDER BY User ASC, CreatedAtDate ASC

echo_Me · Accepted Answer · 2014-04-13 13:18:21Z

0

try this:

   SELECT 
  SUM(CASE 
         WHEN pstatus = "PLAYING" THEN 1
         ELSE 0
       END) AS sumPLAYING,
   SUM(CASE 
         WHEN pstatus = "IDLE" THEN 1
         ELSE 0
       END) AS sumIDLE,

   SUM(CASE 
         WHEN pstatus = "BUFFERING" THEN 1
         ELSE 0
       END) AS sumBUFFERING,
   SUM(CASE 
         WHEN pstatus = "STOPPED" THEN 1
         ELSE 0
       END) AS sumSTOPPED
FROM YOUR_TABLE

answered Apr 13, 2014 at 13:18

echo_Me

37.2k5 gold badges62 silver badges81 bronze badges

Collectives™ on Stack Overflow

Subquery with COUNT (mysql)

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related