Query consolidated grand total of using union

#1

Dear experts,
I want a query of consolidated grand total of both queries

CREATE TABLE T1
(
MID VARCHAR(10),
GPID VARCHAR(2)
)

INSERT INTO T1 VALUES ('MOO1','FT')
INSERT INTO T1 VALUES ('MOO1','FT')
INSERT INTO T1 VALUES ('MOO2','PY')
INSERT INTO T1 VALUES ('MOO2','PY')
INSERT INTO T1 VALUES ('MOO2','PY')
INSERT INTO T1 VALUES ('MOO2','PY')

select count(1),GPID from T1
WHERE MID IN('MOO1')
GROUP BY ROLLUP(GPID)
UNION
select count(1),GPID from T1
where MID IN('MOO2')
GROUP BY ROLLUP(GPID)

Required output

count GPID
2 FT
4 PY
6 TOTAL

#2

why not

select ISNULL(gpid, 'TOTAL') as GPID, count(gpid) as [Count] from @t1
WHERE MID IN('MOO1', 'MOO2')
GROUP BY ROLLUP(GPID)
1 Like
#3

Thanks for the reply,I have small changes in my requirement now.

CREATE TABLE T1
(
MID VARCHAR(10),
GPID VARCHAR(2),
TRAN_DATE DATE
)

Insert into T1 (MID,GPID,TRAN_DATE) values ('MOO1','FT','2017-11-12');
Insert into T1 (MID,GPID,TRAN_DATE) values ('MOO1','FT','2018-11-27');
Insert into T1 (MID,GPID,TRAN_DATE) values ('MOO2','PY','2018-01-15');
Insert into T1 (MID,GPID,TRAN_DATE) values ('MOO2','PY','2018-01-14');
Insert into T1 (MID,GPID,TRAN_DATE) values ('MOO2','PY','2017-12-28');
Insert into T1 (MID,GPID,TRAN_DATE) values ('MOO2','PY','2017-12-25');

DESIRED OUPUT

PERIOD FT PY TOTAL
LAST 90 DAYS 2 4 6
LAST 30 DAYS 0 4 4
LAST 7 DAYS 0 2 2

please help

#4

Maybe I need coffee but I can't see what logic you need to get the output from the input.