CODEWITHSUNDEEP
mysql
Jefferson Rosa
Jefferson Rosa

Reputation: 125

Mysql Group by date with subquery in column

I have this table:

id  datetime            company adset
1   2019-04-08 15:12:11 1       1
2   2019-04-08 14:04:32 2       1
3   2019-04-08 14:04:45 1       1
4   2019-04-07 23:29:55 2       2
5   2019-04-06 21:14:35 2       2

And I'm trying to get the last 30 days in order by date from each company the total of adsets. My subquery is almost there but I still need to group or join the company.

The neareast from this still count wrong companys

SELECT
    DATE(datetime), 
    COUNT(id) as `total_leads`, 
    COUNT(company) as `company`, 
    COUNT(adset) as `adset` 
FROM `leads` 
WHERE datetime between (CURDATE() - INTERVAL 1 MONTH ) AND CURDATE() 
GROUP BY DATE(datetime), company
ORDER BY DATE(datetime) DESC

but my query need the columns from each company, I'm getting this result actually:

DATE(datetime)  total_leads company adset
2019-04-07      94          94      94
2019-04-06      104         104     104
2019-04-05      93          93      93

The final report should be something like:

date        total_leads c1_adset1   c2_adset1   c2_adset2
2019-04-08  15          5           7           3
2019-04-07  12          3           7           2
2019-04-06  10          3           3           4

How can I get the total leads by date from company and from each adset? I had to separate by adsets because the company 2 use the adset 1 and 2 and the company 1 use only the adset 1.

Any help will be great appreciated!

Upvotes: 0

Views: 955

Answers (1)

GMB
GMB

Reputation: 222412

It is hard to really tell for sure because your expected result do not really match your sample data, but I suspect that you are looking for conditional aggregation:

SELECT
    DATE(datetime) AS `date`, 
    COUNT(*) as `total_leads`, 
    SUM(company = 1 AND adset = 1) AS `c1_adset1`,
    SUM(company = 1 AND adset = 2) AS `c1_adset2,
    SUM(company = 2 AND adset = 1) AS `c2_adset1`,
    SUM(company = 2 AND adset = 2) AS `c2_adset2`
FROM `leads` 
WHERE datetime between (CURDATE() - INTERVAL 1 MONTH ) AND CURDATE() 
GROUP BY DATE(datetime)
ORDER BY `date` DESC

Each SUM() actually computes how many records, for each day, match the inner condition.

Upvotes: 1

Related Questions