Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1045
  • Last Modified:

Group by with Rollup in SQL2000

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
hp746
Asked:
hp746
  • 5
  • 3
  • 2
1 Solution
 
mfreudenCommented:
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
 
Chris LuttrellSenior Database ArchitectCommented:
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
 
hp746Author Commented:
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
hp746Author Commented:
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
 
mfreudenCommented:
simply adding submitter and receiptdate to the group by clause doesn't do it?  
0
 
Chris LuttrellSenior Database ArchitectCommented:
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
 
hp746Author Commented:
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
 
Chris LuttrellSenior Database ArchitectCommented:
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
 
hp746Author Commented:
Thank you much.You really helped me lot.
Before close this question..Let me get confirmed from end user.
0
 
hp746Author Commented:
Your genious.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 5
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now