how to select every month even they do not have value

Question

I want to select company id, date and number from table, but this query does not show the month some companies with 0 number.

Here is the query:

SELECT c.name, date_format(e.created, '%y_%m') AS date, count(*) 
FROM company c
JOIN edited e
on c.id=e.company_id AND e.created >='2016-12-13 00:00:00' AND e.created <='2017-05-20 00:00:00'
GROUP BY c.id, date

Some of results like this 4 16_12 2 4 17_01 4 4 17_04 2 4 17_05 2 without 17_03 (march). how can it show 17_03 with 0 ?

can you provide the schema of the relevant tables as well as sample data and expected output? — Jay S., May 26 '17 at 19:52
I have three tables, company(id 1, name a / id 2, name a / id 3, name c), edited(id 1, send_id 1, created 2017-01-01/ id2,send_id4,created 2017-02-01/id3,send_id6,created2017-02-22) and send(id1, company_id1, created2017-01-01/id2,company_id1,created2017-02-02/ id3,company_id 1, created2017-05-01/ id4,company_id 1,created2017-02-01/ id5,company_id2,created2016-12-01/ id6,company_id2,created2017-02-22) — Meng Gao, May 30 '17 at 18:04
the format changed when I add comment, hope you can know what I mean. for edited table, company may not have data in every month, and I am not sure send table contains data for every month. How can I select values for every month, and for months without value, it can show: 2017-04 0(which is cell under count column.) — Meng Gao, May 30 '17 at 18:08

score 0 · Answer 1 · answered May 26 '17 at 20:08

If your join not match values according to its in ON clause, not will return tuples and consequently you won't count rows. If you have a fixed set of companies and dates, you can set manually your values with UNION statement:

(SELECT 'company_name1', 'date1', COUNT(*)
FROM company c
JOIN edited e
on c.id=e.company_id AND e.created >='2016-12-13 00:00:00' AND e.created <='2017-05-20 00:00:00')
UNION

(SELECT 'company_name2', 'date2', COUNT(*)
FROM company c
JOIN edited e
on c.id=e.company_id AND e.created >='2016-12-13 00:00:00' AND e.created <='2017-05-20 00:00:00')

#and make this for companies that not will return zero at count
UNION
(SELECT c.name, date_format(e.created, '%y_%m') AS date, count(*) 
FROM company c
JOIN edited e
on c.id=e.company_id AND e.created >='2016-12-13 00:00:00' AND e.created <='2017-05-20 00:00:00'
GROUP BY c.id, date)

score 0 · Answer 2 · answered May 26 '17 at 21:20

0

SELECT c.id
     , ym.y
     , ym.m
     , count(e.created) cnt
  FROM ( SELECT @startDate := date_add(@startDate, interval 1 month) date
              , year(@startDate) y
              , month(@startDate) m
           FROM HugeTable
             JOIN ( SELECT @startDate := '2016-11-01' 
                         , @endDate   := '2017-05-01'
                  ) months
           WHERE @startDate < @endDate
       ) ym
    LEFT JOIN edited e
      ON    year(e.created)  = ym.y
        AND month(e.created) = ym.m
    LEFT JOIN company c
      ON c.id = e.company_id
  GROUP BY ym.y
         , ym.m
         , c.id

HugeTable can be any table with enough number of records as number of months to be displayed.

answered May 26 '17 at 21:20

Sal

1,307
1
8
16

Thanks for answer. Huge Table should have every records but I don't have a table like that. Is there any other query can work? – Meng Gao May 30 '17 at 14:02
Just use **edited** as HugeTable and it will be fine. My guess. – Sal May 30 '17 at 20:47
Under certain circumstances it is not crazy to have a table of dates since could be more performant than creating rows on the fly. Check [this](https://stackoverflow.com/questions/14105018/generating-a-series-of-dates). – Sal May 30 '17 at 21:03

how to select every month even they do not have value

2 Answers2

Linked