CODEWITHSUNDEEP
mysql
Andy
Andy

Reputation: 138

Trying to group query by hours

Trying to group my query by hours and have a '0' if nothing is found.

SELECT 
tmhours.hours_value,
COALESCE(cc.countingSheep,0) AS countingSheep
FROM time_hours as tmhours
LEFT JOIN (
SELECT count(*) as countingSheep, company_id, `sales_date`
FROM tbl_cc 
WHERE `sales_date` BETWEEN '2019-05-01 00:00:00' AND '2019-05-01 23:59:59' AND company_id = '12345'  ) as cc on date_format(sales_date, '%H') = tmhours.hours_value
GROUP BY tmhours.hours_value

The time_hours table just contains 01,02,03,04 .... 22, 23

Based on the above query, I am just getting 0's until 07

So:

01 0
02 0
03 0
04 0
05 0
06 0
07 - 57 (the first match in the DB is 07:14:35) - the 57 is the total count, it's not grouping results
08 0
09 0
...
...
22 0
23 0

I've tried removing the group by inside the inner select, tried moving the date_format = hours_value.

Upvotes: 0

Views: 19

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133360

You have not aggregated function in the outer query so If you need distinct result use DISTINCT (group by can produce unexpected result ) but in your case seems not necessary

insteadd you missed the group by based on the hour in the inner join 

  SELECT 
    tmhours.hours_value,
    COALESCE(cc.countingSheep,0) AS countingSheep
  FROM time_hours as tmhours
   LEFT JOIN ( SELECT count(*) as countingSheep, company_id, date_format(sales_date, '%H')
      FROM tbl_cc 
      WHERE `sales_date` BETWEEN '2019-05-01 00:00:00' AND '2019-05-01 23:59:59' 
       AND company_id = '12345'
       GROUP BY company_id , date_format(sales_date, '%H')
      ) as cc on date_format(sales_date, '%H') = tmhours.hours_value

Upvotes: 1

Nick
Nick

Reputation: 147146

Your problem is that you're not grouping the subquery data by the hour, so your subquery is only returning one row (since it has a COUNT in it). Add grouping to the subquery and it should work fine. Note that you don't need grouping in the outer query as you're not doing any aggregation. Also, since you only want one day's data, you can simplify your WHERE condition using the DATE function.

SELECT 
  tmhours.hours_value,
  COALESCE(cc.countingSheep,0) AS countingSheep
FROM time_hours as tmhours
LEFT JOIN (
  SELECT count(*) as countingSheep, date_format(sales_date, '%H') AS sales_hour
  FROM tbl_cc 
  WHERE DATE(`sales_date`) = '2019-05-01' AND company_id = '12345'
  GROUP BY sales_hour) as cc ON sales_hour = tmhours.hours_value

Upvotes: 1

Related Questions