-1

I have table as below

reported_date url stautus_code country
2022-01-20 example.com 200 India
2022-02-29 example.com 200 US
2022-01-20 example.com 200 France
2022-01-20 sample.com 200 India
2022-02-29 sample.com 200 US
2022-01-20 sample.com 200 France

Result should be of the below table structure

enter image description here

Basically we group by reported_date, country and count where status_code is 200.

1
  • The keyword to search for is "pivot".
    – mustaccio
    Feb 11, 2023 at 16:03

1 Answer 1

0

Do it in 2 steps. First filter and get the counts:

SELECT url, country, LEFT(date,7) AS yyyymm, COUNT(*) AS ct
    FROM ...
    WHERE status = "200"
    GROUP BY 1,2,3
    ORDER BY 1,2,3

Then write the "pivot", using that as input:

SELECT URL, country,
       COALESCE(SUM(IF(yyyymm = '2022-12', ct, 0), 0) AS '2022/12',
       COALESCE(SUM(IF(yyyymm = '2023-01', ct, 0), 0) AS '2023/01',
       ...
    FROM ( the above query )
    GROUP BY 1,2
    ORDER BY 1,2

See this for a way to automate it: http://mysql.rjweb.org/doc.php/pivot

4
  • considering that i have data for few years and i need to group data for each month year, in that case i wont be able to put IF condition with date hardcoded and Alias hardcoded. Is there way that it gets auto populated
    – santosh
    Feb 11, 2023 at 17:33
  • @santosh - That's where my blog with its Stored Procedure becomes useful.
    – Rick James
    Feb 11, 2023 at 17:41
  • My Current Query is select url, count(status_code), DATE_FORMAT(reported_date,'%Y-%m') as label, country from DownloadEntries where status_code=200 group by download_url, DATE_FORMAT(reported_date,'%Y-%m'), country;
    – santosh
    Feb 11, 2023 at 17:52
  • Oops. If fixed that.
    – Rick James
    Feb 11, 2023 at 20:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.