Grouping rows based on "months" SQL

Question

I have a working query that produces the table shown below.

The query:

Select * from (
SELECT months, count(user_id) as count
        from 
        (
        select u.user_id, u.region_id, u.latest_login, year(u.latest_login), 
        
        period_diff(date_format(now(), '%Y%m'),date_format(u.latest_login, '%Y%m')) as months
        
        from users u
        where u.date_ended = 0000-00-00 
            AND country_id = 1 
            AND intRoleId = 3 
        )
        t1
        group by months
)
t2

The table:

I want to group rows based on the "months" number, and have the counts merged. So for a group called "less than 3 months" the result should show 140.

Below is the query that I've tried but the results are not correct. I think maybe I shouldn't be using SUM, but not sure what to use instead. Any suggestions on how to get the result I want?

Select months, count,
sum(months<=2) as months_less_than_3_,
sum(months>=3) as months_3_6,
sum(months>=6) as months_6_12,
sum(months>=12) as months_12_24,
sum(months>=24) as months_25_plus,
sum(months>=20000) as Never


from (
SELECT months, count(user_id) as count
        from 
        (
        select u.user_id, u.region_id, u.latest_login, year(u.latest_login), 
        
        period_diff(date_format(now(), '%Y%m'),date_format(u.latest_login, '%Y%m')) as months
        
        from users u
        where u.date_ended = 0000-00-00 
            AND country_id = 1 
            AND intRoleId = 3 
        )
        t1
        group by months
)
t2

For MariaDB, see See https://stackoverflow.com/a/56670844/1766831 — Rick James, Nov 01 '21 at 02:26

CleanBold · Accepted Answer · 2020-10-27T10:27:14.917

2

    SELECT MonthGroup, SUM(count) as CountGroup 
    FROM 
    (Select 
    CASE 
    WHEN months<=2 THEN 'months_less_than_3'
    WHEN months<=6 THEN  'months_3_6'
    WHEN months<=12 THEN 'months_6_12'
    WHEN months<=24 THEN 'months_12_24'
    WHEN months>=20000 THEN 'Never'
    WHEN months>=25 THEN'months_25_plus'
    END AS MonthGroup, count    
    from (
        SELECT months, count(user_id) as count
        from 
        (
        select u.user_id, u.region_id, u.latest_login, year(u.latest_login), 
        
        period_diff(date_format(now(), '%Y%m'),date_format(u.latest_login, '%Y%m')) as months
        
        from users u
        where u.date_ended = 0000-00-00 
            AND country_id = 1 
            AND intRoleId = 3 
        ) t1
        group by months
        ) t2
    ) t3        
    GROUP BY MonthGroup

edited Oct 27 '20 at 10:27

answered Oct 27 '20 at 10:06

CleanBold

1,551
1
14
37

Thank you so much for your reply. When I try this, I get an error: Query 1 ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ' WHEN months<=6 THEN 'months_3_6', WHEN months<=12 THEN 'months_6_12', WHEN mon' at line 5 – astara303 Oct 27 '20 at 10:14
Edited my query. Removed comma at the end of each WHEN statement. Can you try now? – CleanBold Oct 27 '20 at 10:20
With the edit, I now see this error: Query 1 ERROR: Every derived table must have its own alias – astara303 Oct 27 '20 at 10:24
added t3 alias. can you try now? – CleanBold Oct 27 '20 at 10:31
It works!! Thank you so much, I spent all yesterday looking at this. Really appreciate it – astara303 Oct 27 '20 at 10:32

Grouping rows based on "months" SQL

1 Answers1