Anthony
January 12, 2018, 7:33am
#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
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
Anthony
January 18, 2018, 10:33am
#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
Maybe I need coffee but I can't see what logic you need to get the output from the input.