CODEWITHSUNDEEP
mysql
lilbiscuit
lilbiscuit

Reputation: 2249

mysql query - nested with group by

I want to create a query that returns the amount of leads that came in for each hour of the day, as well as the sales count for each hour. The result is to be 24 rows, 1 for each hour of the day.

I have a query that works for either/or, but how do I combine with one query?

The table 'mytable' is:

id  datetime  status 
--------------------

Here's the query for total:

SELECT FROM_UNIXTIME(datetime, '%H') AS Hour, 
 count(id) AS Count

FROM mytable

GROUP BY Hour

and here's the query for "sold":

SELECT FROM_UNIXTIME(datetime, '%H') AS Hour, 
 count(id) AS Count

FROM mytable

WHERE status='Sold'    

GROUP BY Hour

So I want the result to return something that looks like this:

Hour Count  Sold
----------------
0     5      2
1     4      0
2     9      6
.
.
.
.
23    6      1

I can't figure this out.

Upvotes: 1

Views: 48

Answers (2)

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

In mysql you can use sum with expression,it will result as boolean ,this will give you the count where status is sold

SELECT 
    FROM_UNIXTIME(datetime, '%H') AS Hour, 
    COUNT(id) AS Count,
    SUM(status='Sold' ) sold
FROM mytable
GROUP BY Hour

Upvotes: 1

echo_Me
echo_Me

Reputation: 37233

Try that 

  SELECT CASE WHEN status='Sold' THEN FROM_UNIXTIME(datetime, '%H') ELSE 0 END AS Hour, 
  ,FROM_UNIXTIME(datetime, '%H') AS Hour2 ,count(id) AS Count
  FROM mytable
  GROUP BY Hour

Upvotes: 0

Related Questions