Solved

Group by with Rollup in SQL2000

Posted on 2009-05-20
10
1,024 Views
Last Modified: 2012-05-07
SELECT CASE WHEN (Grouping(rename)=1) THEN '--Total--' ELSE rtrim(rename) END AS "Assigned",
count(tem) AS "Pending",
SUM(CASE WHEN rtrim(key) = 'AGENCY' THEN 1 ELSE 0 END) AS "Agency",
SUM(CASE WHEN rtrim(key) = 'User' THEN 1 ELSE 0 END) AS "User",
submittername as Submitter,
receiptdate  as Date received
 

From table1,table2
Where--------
Group by rename with rollup


User needs Total column and even the Submitter and Receipt Date.My issue as submittername and Receipt Date is not an aggregate I cant get that with rollup.How do I have to include that in the query.
If Iam using that with rollup,Iam having 3 rows with each line missing a piece of data that was on the line before it.
Please advise as Iam using SQL 2000 for this not SQL 2005.
0
Comment
Question by:hp746
  • 5
  • 3
  • 2
10 Comments
 
LVL 3

Expert Comment

by:mfreuden
ID: 24433973
I'm not 100% sure what you're looking for:  But I assume you want to show the submitter and receiptdate in the total row?  And these 2 fields are in your where clase as well?

SELECT CASE WHEN (Grouping(rename)=1) THEN '--Total--' ELSE rtrim(rename) END AS "Assigned",
count(tem) AS "Pending",
SUM(CASE WHEN rtrim(key) = 'AGENCY' THEN 1 ELSE 0 END) AS "Agency",
SUM(CASE WHEN rtrim(key) = 'User' THEN 1 ELSE 0 END) AS "User",
CASE WHEN (Grouping(rename)=1) THEN max(submittername) else null end as Submitter,
CASE WHEN (Grouping(rename)=1) THEN max(receiptdate) ekse null end  as Date_received
From table1,table2
Where--------
Group by rename with rollup
0
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 24434486
I have helped with "with Rollup" questions before.  See if this helps, if not post back better details of your table structure and some sample data and I will take a look at your specific case.
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_24334898.html
0
 

Author Comment

by:hp746
ID: 24435027
No.
I dont want those in Total row.
Here is sample of data and row.Total should do only for Agency and User and submitter and Receipt date as left alone.
Assigned      Pending      Agency      User      Submitter       Receipt Date
HP      1             1       Id746      5/20/2009
HP      1       1             Id747      5/21/2009
HP      1      1              Id748      5/22/2009
                                    
Total      3      2      1            
0
 

Author Comment

by:hp746
ID: 24435766
here is the exact out put..
Assigned      Pending      Agency      User      Submitter       Receipt Date
HP                     1               1                  0           Id746           5/20/2009
HP                     1               1                  0            Id747           5/21/2009
HP                     1               0                  1             Id748          5/22/2009
                                   
Total                     3             2                  1            

0
 
LVL 3

Expert Comment

by:mfreuden
ID: 24437021
simply adding submitter and receiptdate to the group by clause doesn't do it?  
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 24437203
This is how to get the rows you want out of the with rollup.
SELECT CASE WHEN (Grouping(rename)=1) THEN '--Total--' ELSE rtrim(rename) END AS "Assigned",

count(tem) AS "Pending",

SUM(CASE WHEN rtrim([key]) = 'AGENCY' THEN 1 ELSE 0 END) AS "Agency",

SUM(CASE WHEN rtrim([key]) = 'User' THEN 1 ELSE 0 END) AS "User",

submittername AS [Submitter],

receiptdate  as [Date received]

From table1

Group by rename, submittername, receiptdate  with rollup

HAVING Grouping(receiptdate) = 0 OR Grouping(rename) = 1
 

-- version 2 with Total per Assigned and Grand Total
 

SELECT CASE WHEN (Grouping(rename)=1) THEN '--Grand Total--' WHEN (Grouping(submittername)=1) THEN '--Total--' ELSE rtrim(rename) END AS "Assigned",

count(tem) AS "Pending",

SUM(CASE WHEN rtrim([key]) = 'AGENCY' THEN 1 ELSE 0 END) AS "Agency",

SUM(CASE WHEN rtrim([key]) = 'User' THEN 1 ELSE 0 END) AS "User",

submittername AS [Submitter],

receiptdate  as [Date received]

From table1

Group by rename, submittername, receiptdate  with rollup

HAVING Grouping(receiptdate) = 0 OR Grouping(submittername) = 1

Open in new window

With-Rollup-Results.png
0
 

Author Comment

by:hp746
ID: 24442142
Thank you verymuch CGLuttrell:,
almost I got the answer..i need two more columns added to the query same like submittername and receiptdate.Title and log id..Please advise me the changes I put in having.
0
 
LVL 26

Accepted Solution

by:
Chris Luttrell earned 500 total points
ID: 24442512
I added the columns and added them into the "Group By" being CAREFUL to add them before ReceiptDate and after Submittername because the order of the groups is important in the Having clause where we limit which Totals are being displayed.
SELECT CASE WHEN (Grouping(rename)=1) THEN '--Total--' ELSE rtrim(rename) END AS "Assigned",

count(tem) AS "Pending",

SUM(CASE WHEN rtrim([key]) = 'AGENCY' THEN 1 ELSE 0 END) AS "Agency",

SUM(CASE WHEN rtrim([key]) = 'User' THEN 1 ELSE 0 END) AS "User",

submittername AS [Submitter],

receiptdate  as [Date received],

title,

[log id]

From table1

Group by rename, submittername, title, [log id], receiptdate  with rollup

HAVING Grouping(receiptdate) = 0 OR Grouping(rename) = 1
 

-- version 2 with Total per Assigned and Grand Total
 

SELECT CASE WHEN (Grouping(rename)=1) THEN '--Grand Total--' 

		WHEN (Grouping(submittername)=1) THEN '--Total--' 

		ELSE rtrim(rename) END AS "Assigned",

count(tem) AS "Pending",

SUM(CASE WHEN rtrim([key]) = 'AGENCY' THEN 1 ELSE 0 END) AS "Agency",

SUM(CASE WHEN rtrim([key]) = 'User' THEN 1 ELSE 0 END) AS "User",

submittername AS [Submitter],

receiptdate  as [Date received],

title,

[log id]

From table1

Group by rename, submittername, title, [log id], receiptdate  with rollup

HAVING Grouping(receiptdate) = 0 OR Grouping(submittername) = 1

Open in new window

With-Rollup-Results2.png
0
 

Author Comment

by:hp746
ID: 24442834
Thank you much.You really helped me lot.
Before close this question..Let me get confirmed from end user.
0
 

Author Closing Comment

by:hp746
ID: 31583590
Your genious.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now