How to group by with a case

Question

I have a script that collates the login logs between two dates, and categories them as one of three time periods. I need it to then group them together to tally the number of unique users that have logged into the system during those periods. For example:

SQL:

select
    case
        when lsl.timecreated between cast(datediff(second,'01-01-1970 00:00:00','2018-07-23 00:00:00') as varchar) and cast(datediff(second,'01-01-1970 00:00:00','2018-12-09 23:59:59') as varchar) -- Semester 2, 2018: 23/07/2018 to 09/12/2018
            then 'Semester 2, 2018'
        when lsl.timecreated between cast(datediff(second,'01-01-1970 00:00:00','2018-12-10 00:00:00') as varchar) and cast(datediff(second,'01-01-1970 00:00:00','2018-02-03 23:59:59') as varchar) -- Christmas Break, 2018-19: 10/12/2018 to 03/02/2019
            then 'Christmas Break, 2018-19'
        when lsl.timecreated between cast(datediff(second,'01-01-1970 00:00:00','2019-02-04 00:00:00') as varchar) and cast(datediff(second,'01-01-1970 00:00:00','2019-06-23 23:59:59') as varchar) -- Semester 1, 2019: 04/02/2019 to 23/06/2019
            then 'Semester 1, 2019'
        else 'Christmas Break, 2018-19'
    end as 'Login Date',
    count(distinct lsl.userid) as '# of unique logins',
    lsl.action
from prefix_logstore_standard_log lsl
where lsl.action = 'loggedin' and
    (lsl.timecreated between cast(datediff(second,'01-01-1970 00:00:00','2018-07-23 00:00:00') as varchar) and cast(datediff(second,'01-01-1970 00:00:00','2019-06-23 23:59:59') as varchar))
group by lsl.timecreated, lsl.action
order by lsl.timecreated asc

This lists the data on individual rows, as expected:

------------------------------------------------------------
| Login Date               | # of Unique Logins | action   |
------------------------------------------------------------
| Semester 2, 2018         | 1                  | loggedin |
| Semester 2, 2018         | 1                  | loggedin |
| Semester 2, 2018         | 1                  | loggedin |
| Semester 2, 2018         | 1                  | loggedin |
| Christmas Break, 2018-19 | 1                  | loggedin |
| Christmas Break, 2018-19 | 1                  | loggedin |
| Christmas Break, 2018-19 | 1                  | loggedin |
| Semester 1, 2019         | 1                  | loggedin |
| Semester 1, 2019         | 1                  | loggedin |
------------------------------------------------------------

I need to group the Login Date as below. How do I do this?

------------------------------------------------------------
| Login Date               | # of Unique Logins | action   |
------------------------------------------------------------
| Semester 2, 2018         | 4                  | loggedin |
| Christmas Break, 2018-19 | 3                  | loggedin |
| Semester 1, 2019         | 2                  | loggedin |
------------------------------------------------------------

I've attempted adding the same case to the group by, but it throws an error I don't understand:

...
group by
    case
        when lsl.timecreated between cast(datediff(second,'01-01-1970 00:00:00','2018-07-23 00:00:00') as varchar) and cast(datediff(second,'01-01-1970 00:00:00','2018-12-09 23:59:59') as varchar) -- Semester 2, 2018: 23/07/2018 to 09/12/2018
            then 'Semester 2, 2018'
        when lsl.timecreated between cast(datediff(second,'01-01-1970 00:00:00','2018-12-10 00:00:00') as varchar) and cast(datediff(second,'01-01-1970 00:00:00','2018-02-03 23:59:59') as varchar) -- Christmas Break, 2018-19: 10/12/2018 to 03/02/2019
                    then 'Christmas Break, 2018-19'
        when lsl.timecreated between cast(datediff(second,'01-01-1970 00:00:00','2019-02-04 00:00:00') as varchar) and cast(datediff(second,'01-01-1970 00:00:00','2019-06-23 23:59:59') as varchar) -- Semester 1, 2019: 04/02/2019 to 23/06/2019
            then 'Semester 1, 2019'
    end,
    lsl.action
order by lsl.timecreated asc

Error:

---

SQLState: 42000 Error Code: 8120 Column prefix_logstore_standard_log.timecreated is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

---

Answer 1

With CTE (Common Table Expression) it should solve your problem.

Unfortunately, I don't have your data to test, however, the below should work:

with cte ([Login Date],[# of Unique Logins],[action])
as
(
select
    case
        when lsl.timecreated between cast(datediff(second,'01-01-1970 00:00:00','2018-07-23 00:00:00') as varchar) and cast(datediff(second,'01-01-1970 00:00:00','2018-12-09 23:59:59') as varchar) -- Semester 2, 2018: 23/07/2018 to 09/12/2018
            then 'Semester 2, 2018'
        when lsl.timecreated between cast(datediff(second,'01-01-1970 00:00:00','2018-12-10 00:00:00') as varchar) and cast(datediff(second,'01-01-1970 00:00:00','2018-02-03 23:59:59') as varchar) -- Christmas Break, 2018-19: 10/12/2018 to 03/02/2019
            then 'Christmas Break, 2018-19'
        when lsl.timecreated between cast(datediff(second,'01-01-1970 00:00:00','2019-02-04 00:00:00') as varchar) and cast(datediff(second,'01-01-1970 00:00:00','2019-06-23 23:59:59') as varchar) -- Semester 1, 2019: 04/02/2019 to 23/06/2019
            then 'Semester 1, 2019'
        else 'Christmas Break, 2018-19'
    end as 'Login Date',
    count(distinct lsl.userid) as '# of unique logins',
    lsl.action
from prefix_logstore_standard_log lsl
where lsl.action = 'loggedin' and
    (lsl.timecreated between cast(datediff(second,'01-01-1970 00:00:00','2018-07-23 00:00:00') as varchar) and cast(datediff(second,'01-01-1970 00:00:00','2019-06-23 23:59:59') as varchar))
group by lsl.timecreated, lsl.action
order by lsl.timecreated asc
)

select
*
from    cte
group by [Login Date],[# of Unique Logins],[action]

Let me know if it works.

Answer 2

use  cte 


    with cte as
(
Select
    Vouchers.Id as Id,
   FromLedgers.Name as FromLedger,
    ToLedgers.Name As ToLedger,
    Case 
    When Vouchers.[LedgerFromId] = 1 then Vouchers.[Amount] Else 0 
    End as Debit,
    Case 
    When Vouchers.[LedgerToId] = 1 then Vouchers.[Amount] Else 0 
    End as Credit
  From [dbo].[Ledgers] as FromLedgers 
 Inner Join Vouchers On Vouchers.LedgerFromId = FromLedgers.Id 
 Inner Join Ledgers as ToLedgers On Vouchers.LedgerToId = ToLedgers.Id

)


select cte.FromLedger,cte.ToLedger,cte.Credit,cte.Debit ,Sum(cte2.Credit-cte2.Debit)+(select Ledgers.OpeningBalance from Ledgers where Id=1) as Balance 

  from cte inner join cte as cte2 on cte2.Id <= cte.Id 
 group by cte.Id, cte.FromLedger,cte.ToLedger,cte.Credit,cte.Debit 

Answer 3

you can just aggregate using an outer query

select "Login Date" , count("# of unique logins") , action
from (
    select
        case
            when lsl.timecreated between cast(datediff(second,'01-01-1970 00:00:00','2018-07-23 00:00:00') as varchar) and cast(datediff(second,'01-01-1970 00:00:00','2018-12-09 23:59:59') as varchar) -- Semester 2, 2018: 23/07/2018 to 09/12/2018
                then 'Semester 2, 2018'
            when lsl.timecreated between cast(datediff(second,'01-01-1970 00:00:00','2018-12-10 00:00:00') as varchar) and cast(datediff(second,'01-01-1970 00:00:00','2018-02-03 23:59:59') as varchar) -- Christmas Break, 2018-19: 10/12/2018 to 03/02/2019
                then 'Christmas Break, 2018-19'
            when lsl.timecreated between cast(datediff(second,'01-01-1970 00:00:00','2019-02-04 00:00:00') as varchar) and cast(datediff(second,'01-01-1970 00:00:00','2019-06-23 23:59:59') as varchar) -- Semester 1, 2019: 04/02/2019 to 23/06/2019
                then 'Semester 1, 2019'
            else 'Christmas Break, 2018-19'
        end as 'Login Date',
        count(distinct lsl.userid) as '# of unique logins',
        lsl.action
    from prefix_logstore_standard_log lsl
    where lsl.action = 'loggedin' and
        (lsl.timecreated between cast(datediff(second,'01-01-1970 00:00:00','2018-07-23 00:00:00') as varchar) and cast(datediff(second,'01-01-1970 00:00:00','2019-06-23 23:59:59') as varchar))
    group by lsl.timecreated, lsl.action
    order by lsl.timecreated asc
) as a
group by "Login Date" , action