CODEWITHSUNDEEP
sqlsql-server
Vishal Dhasal
Vishal Dhasal

Reputation: 61

How to perform group by in SQL Server for specific output

I have a table with few records, I want to get month wise data along with count on one of the column. The output should contain Month and count of Isregistered flag.

Table structure

| Inserted On | IsRegistered |
+-------------+--------------+
| 10-01-2020  | 1            |
| 15-01-2020  | 1            |
| 17-01-2020  | null         |
| 17-02-2020  | 1            |
| 21-02-2020  | null         |   
| 04-04-2020  | null         |
| 18-04-2020  | null         |
| 19-04-2020  | 1            |

Excepted output

| Inserted On | Registered | Not Registered
+-------------+------------+---------------
| Jan         |     2      |    1
| Feb         |     1      |    1
| Apr         |     1      |    2

I tried by performing normal group by but didn't got desired output

SELECT
    DATENAME(MONTH, dateinserted) AS [MonthName], COUNT(ISRegistered)
FROM
    tablename 
GROUP BY
    (DATENAME(MONTH, dateinserted))

Note: here null is treated as not registered

Upvotes: 1

Views: 54

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269543

You can use aggregation. I would include the year and use the month number rather than name, so:

select year(inserted_on), month(inserted_on),
       coalesce(sum(is_registered), 0) as num_registered,
       sum(case when is_registered is null then 1 else 0 end) as num_not_registered
from tablename
group by year(inserted_on), month(inserted_on)
order by year(inserted_on), month(inserted_on);

Note: If you really want the monthname and want to combine data from different years (which seems unlikely, but . . . ), then you can use:

select datename(month, inserted_on),
       coalesce(sum(is_registered), 0) as num_registered,
       sum(case when is_registered is null then 1 else 0 end) as num_not_registered
from tablename
group by datename(month, inserted_on)
order by month(min(inserted_on));

Upvotes: 1

SteveC
SteveC

Reputation: 6015

The GROUP BY should include both the year and month (so there's no overlapping) as well as the DATENAME (for display). Something like this

drop table if exists #tablename;
go
create table #tablename(dateinserted date, ISRegistered int);
insert #tablename values 
('2020-12-01', 0),
('2020-11-02', 1),
('2020-11-03', 1),
('2020-12-01', 1),
('2020-12-03', 1),
('2020-11-02', 0);

select year(dateinserted) yr,
       datename(month, dateinserted) AS [MonthName], 
       sum(ISRegistered) Registered ,
       sum(1-ISRegistered) [Not Registered]
from #tablename 
group by year(dateinserted), month(dateinserted), datename(month, dateinserted)
order by year(dateinserted), month(dateinserted);

yr      MonthName   Registered  Not Registered
2020    November    2           1
2020    December    2           1

Upvotes: 0

Related Questions