group by roll up
-
I am pivoting against a dataset and that works, but I want to add GROUP BY ROLLUP so that I can have sum totals for each empid by month and year.
like this:
create table #data(yearOrdered varchar(20), moOrdered varchar(50), dayordered varchar(20), empid1 int, empid2 int, empid3 int);
insert into #data select '2007', '11', '14', 2,2,1
insert into #data select '2007', '12', '24', 0,0,1
insert into #data select '2007', '12', '25', 1,1,1
insert into #data select '2007', NULL, NULL, 3,3,3
insert into #data select '2007', '11', NULL, 2,2,1
insert into #data select '2007', '12', NULL, 1,1,2
select * from #data;
How to make GROUP BY ROLLUP work so that i can see totals for empid by year and by month?
Below is DDL that will provide the data set that is the basis of below pivot, etc queries for quick recreation of environment.
---DDL
USE tempdb;IF OBJECT_ID('dbo.Orders', 'U') IS NOT NULL DROP TABLE dbo.Orders;
CREATE TABLE dbo.Orders
(
orderid INT NOT NULL,
orderdate DATE NOT NULL,
empid INT NOT NULL,
custid VARCHAR(5) NOT NULL,
qty INT NOT NULL,
CONSTRAINT PK_Orders PRIMARY KEY(orderid)
);
INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
VALUES
(30001, '20070802', 3, 'A', 10),
(10001, '20071224', 2, 'A', 12),
(10005, '20071224', 1, 'B', 20),
(40001, '20080109', 2, 'A', 40),
(10006, '20080118', 1, 'C', 14),
(20001, '20080212', 2, 'B', 12),
(40005, '20090212', 3, 'A', 10),
(20002, '20090216', 1, 'C', 20),
(30003, '20090418', 2, 'B', 15),
(30004, '20070418', 3, 'C', 22),
(30007, '20090907', 3, 'D', 30);
---pivot works
select dayOrdered
, [1]
, [2]
, [3]
from
(
select DAY(orderdate) as dayOrdered, empid, orderid
from dbo.Orders
) as cte
pivot (count(orderid) for empid in ([1], [2], [3])) as pvt;
---pivot with GROUP BY ROLLUP doesn’t work
SELECT * FROM dbo.Orders;
select dayOrdered
, [1]
, [2]
, [3]
from
(
select DAY(orderdate) as dayOrdered, MONTH(orderdate) as moOrdered,YEAR(orderdate)as yrOrdered, empid, orderid
from dbo.Orders
) as cte
pivot (count(orderid) for empid in ([1], [2], [3])) as pvt
GROUP BY ROLLUP(yrOrdered, moOrdered);
--Quote me
-
select empid,datepart(yyyy,orderdate) [Year],
datepart(mm,orderdate) [Month]
,SUM(qty) [TotalQty]
from Orders
Group by empid,datepart(yyyy,orderdate),datepart(mm,orderdate)
WITH ROLLUP;
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
Lokesh Vij (7/28/2012)
select empid,datepart(yyyy,orderdate) [Year],
datepart(mm,orderdate) [Month]
,SUM(qty) [TotalQty]
from Orders
Group by empid,datepart(yyyy,orderdate),datepart(mm,orderdate)
WITH ROLLUP;
Needless to say, you need to expend this in you pivot for your result.
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
Lokesh, I have seen that information too, but it is the 'expansion into' pivot that is the problem and for which I can't find demo.
EG, I see how to apply group by with rollup when there isn't pivoting happening:
---this works
select YEAR(orderdate)as yrOrdered, MONTH(orderdate) as moOrdered, empid, COUNT(orderid) as count
from dbo.Orders
GROUP BY YEAR(orderdate), MONTH(orderdate), empid
WITH ROLLUP;
Can you do the 'expend' and show me combining PIVOT operator with GROUP BY WITH ROLLUP using the ddl I provided?
--Quote me
-
Still having problem adding pivot to group by with rollup sql query. Can anyone help?
SELECT yrOrdered, moOrdered, DayOrdered, COUNT(orderid) as countofOrders
from
(
select YEAR(orderdate)as yrOrdered, MONTH(orderdate) as moOrdered, DAY(orderdate) as dayOrdered, empid, orderid
from dbo.Orders
)as c
GROUP BY yrOrdered, moOrdered, DayOrdered
WITH ROLLUP;
how to add pivot operator so that data spreads horizontally by empid 1, 2, 3?
--Quote me
-
solved syntax of adding pivot, am still analyzing results. At least this runs without error.
SELECT yrOrdered, moOrdered, DayOrdered, [1], [2], [3]
from
(
select YEAR(orderdate)as yrOrdered, MONTH(orderdate) as moOrdered, DAY(orderdate) as dayOrdered, empid, orderid
from dbo.Orders
)as c
pivot (count(orderid) for empid in ([1], [2], [3])) as pvt
GROUP BY yrOrdered, moOrdered, DayOrdered, [1], [2], [3]
WITH ROLLUP;
--Quote me
-
to avoid rollup by empid I used sum functions with each which allowed me to group by with rollup against just yrOrdered and moOrdered.
This query produces the results I wanted:
SELECT yrOrdered, moOrdered, sum([1]) as EMP1, sum([2]) as EMP2, sum([3]) as EMP3
from
(
select YEAR(orderdate)as yrOrdered, datename(month, orderdate) as moOrdered, DAY(orderdate) as dayOrdered, empid, orderid
from dbo.Orders
)as c
pivot (count(orderid) for empid in ([1], [2], [3])) as pvt
GROUP BY yrOrdered, moOrdered
WITH ROLLUP;
What I am having trouble is (as is often the case), is proper syntax, which in turn is a result of my having trouble understanding the underlying set logic. Why the query doesn't work when I add orderid to the outer query, is a mystery to me? How is that illogical, when I have to aggregate it in the pivot?
--Quote me
-
polkadot (7/27/2012)
How to make GROUP BY ROLLUP work so that i can see totals for empid by year and by month?It depends. Why are you trying to show 3 EmpIDs for each row of your output?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply