tanyaa tanyaa - 3 months ago 11
MySQL Question

sql query doesn't give me all the months

I have a "customers" table, with many columns. I need to use only 2 of them. date(the date that the customer was registerd), referrer( how did the customer find us: google or facebook or friends). I need for each month to show how many customers added by each referrer. for example: 2016-8 4 customers by facebook, 10 customers by friends and 13 customers by google). of course it should give me for each month the result in the example.

I tried this query - but it doesn't give me all the months only 2 of the months and one of them is being shown twice. can someone please review my query?

SELECT referrer,

date
, COUNT(
date
)

FROM
customers


GROUP BY
referrer
;

Answer

Try this:

SELECT referrer,date, COUNT(date)
FROM customers
GROUP BY Year(date), MONTH(date), referrer;

If you want to select the output for a certain year you can try this:

SELECT referrer,date, COUNT(date)
FROM customers
WHERE record_date.YEAR = 2016
GROUP BY Year(date), MONTH(date), referrer;