Your communication platform is only as good as the relevance of the information you send. Ensure your alerts get to the right people every time with actionable responses. Create escalation rules that ensure everyone follows the process and nothing is left to chance.
Course Of The Month
1 day, 19 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Using aggregate rollup data
Posted on 2012-12-21
I am using a query for totals using group by with roll up. The query looks like this:
SELECT COALESCE(CAST(A.TopicID AS varchar(2000)), 'Report Total:') as 'Conference', COUNT(*) AS 'Number of Entries', A.TopicName, SUM(A.Amount) AS Sum FROM vwApplication A INNER JOIN Topic B ON A.Topic = B.Topic INNER JOIN ConfCity C ON B.ConfCityFK = C.ConfCityKEYID WHERE MoneyReceived = 1 AND Amount > 0 GROUP BY A.TopicID WITH ROLLUP
dataset = Conference #entries Sum
44 4 4500
45 5 4600
I would like to pull the name associated with this TopicID Column
Any help with this would be greatly appreciated.
SELECT COALESCE(CAST(A.TopicID AS varchar(2000)), 'Report Total:') as 'Conference', COUNT(*) AS 'Number of Entries', A.TopicName, SUM(A.Amount) AS Sum FROM vwApplication A INNER JOIN Topic B ON A.Topic = B.Topic INNER JOIN ConfCity C ON B.ConfCityFK = C.ConfCityKEYID WHERE MoneyReceived = 1 AND Amount > 0 GROUP BY A.TopicID WITH ROLLUP
dataset = Conference #entries Sum
44 4 4500
45 5 4600
I would like to pull the name associated with this TopicID Column
Any help with this would be greatly appreciated.
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
7
5
3- +1
16 Comments
inluce topic name in the group by:
GROUP BY A.TopicID, A.TopicName
SELECT ISNULL(CAST(A.TopicID AS VARCHAR(2000)), [Report Total:]) AS [Conference] ,
ISNULL(A.TopicName, '') [Topic Name] ,
COUNT(*) AS [Number of Entries] ,
SUM(A.Amount) AS Sum
FROM vwApplication A
INNER JOIN Topic B ON A.Topic = B.Topic
INNER JOIN ConfCity C ON B.ConfCityFK = C.ConfCityKEYID
WHERE MoneyReceived = 1
AND Amount > 0
GROUP BY A.TopicID , A.TopicName
WITH ROLLUP
I tried that and I got repeats
Conference Topic Name Number of Entries Sum
11510 Regulation and Function of Small GTPases 1 1015.00
11510 1 1015.00
11531 Proteases in Hemostasis & Vascular Biology 1 1450.00
11531 1 1450.00
11637 Hematologic Malignancies 1 1465.00
11637 1 1465.00
11807 Membrane Organization by Membrane Scaffolds 1 1300.00
11807 1 1300.00
[Report Total:] 4 7180.00
Conference Topic Name Number of Entries Sum
11510 Regulation and Function of Small GTPases 1 1015.00
11510 1 1015.00
11531 Proteases in Hemostasis & Vascular Biology 1 1450.00
11531 1 1450.00
11637 Hematologic Malignancies 1 1465.00
11637 1 1465.00
11807 Membrane Organization by Membrane Scaffolds 1 1300.00
11807 1 1300.00
[Report Total:] 4 7180.00
Active today
In Oracle adding brackets in the GROUP BY sequence would remove the unwanted rows - might work in mssql:
SELECT ISNULL(CAST(A.TopicID AS VARCHAR(2000)), [Report Total:]) AS [Conference] ,
ISNULL(A.TopicName, '') [Topic Name] ,
COUNT(*) AS [Number of Entries] ,
SUM(A.Amount) AS Sum
FROM vwApplication A
INNER JOIN Topic B ON A.Topic = B.Topic
INNER JOIN ConfCity C ON B.ConfCityFK = C.ConfCityKEYID
WHERE MoneyReceived = 1
AND Amount > 0
GROUP BY (A.TopicID , A.TopicName) /* small tweak */
WITH ROLLUP
I would like to pull the name associated with this TopicID Column
Actually, I am confused.
1. You have added A.TopicName so we can only assume that TopicName is already in the VIEW. Were you intending the TopicName to come from the Topic table instead?
2. In order to help you, we need to see the contents of the VIEW vwApplication.
3. Please add the appropriate aliases to the columns in the WHERE clause.
Actually, I am confused.
1. You have added A.TopicName so we can only assume that TopicName is already in the VIEW. Were you intending the TopicName to come from the Topic table instead?
2. In order to help you, we need to see the contents of the VIEW vwApplication.
3. Please add the appropriate aliases to the columns in the WHERE clause.
Active today
I believe you are seeking something like this:
try this:
Also see: http://msdn.microsoft.com/
Conference Topic Name Number of Entries Sum
11510 Regulation and Function of Small GTPases 1 1015.00
11531 Proteases in Hemostasis & Vascular Biology 1 1450.00
11637 Hematologic Malignancies 1 1465.00
11807 Membrane Organization by Membrane Scaffolds 1 1300.00
Report Total: 4 5230.00
try this:
SELECT
ISNULL(CAST(A.TopicID AS VARCHAR(2000)), [Report Total:]) AS [Conference]
, ISNULL(A.TopicName, '') AS [Topic Name]
, COUNT(*) AS [Number of Entries]
, SUM(A.Amount) AS Sum
FROM vwApplication A
INNER JOIN Topic B ON A.Topic = B.Topic
INNER JOIN ConfCity C ON B.ConfCityFK = C.ConfCityKEYID
/* you really should have aliases against the following fields */
WHERE MoneyReceived = 1
AND Amount > 0
GROUP BY GROUPING SETS(
(A.TopicID , A.TopicName)
)
Also see: http://msdn.microsoft.com/
I tried several variations and can see what this is trying to accomplish, but it will not work. Thank you for your help with this.
Repeats means that if I am grouping by topic number, I should not see the same topic number in my results more than once. The columns in the where clause are in the vwApplication. They arent important. What is important is that I get the topicname associated with the topic number when using rollup. I think portletpauls solution will work but the syntax is incorrect.
acperkins,
I added parens and that did not work.
1. No the topicname is in the vwapplication. I had the join in the query cause I copied and pasted from another query. The join can be cut from the query and it will work the same.
2. All of the columns I am referencing are in the view
3. The where clause does not matter either
I added parens and that did not work.
1. No the topicname is in the vwapplication. I had the join in the query cause I copied and pasted from another query. The join can be cut from the query and it will work the same.
2. All of the columns I am referencing are in the view
3. The where clause does not matter either
I added parens and that did not work.
That was not me (but see below)
I think portletpauls solution will work but the syntax is incorrect.
Actually the syntax is perfectly correct (I just tested it) at least if you are using SQL Server 2008, as to whether the output is correct, I honestly have no idea as I am not following you at all. If you would like to supply the schema for all the tables, a SQL Script that populates then with some sample data and tell us what is the expected output, I would be happy to take a second look.
That was not me (but see below)
I think portletpauls solution will work but the syntax is incorrect.
Actually the syntax is perfectly correct (I just tested it) at least if you are using SQL Server 2008, as to whether the output is correct, I honestly have no idea as I am not following you at all. If you would like to supply the schema for all the tables, a SQL Script that populates then with some sample data and tell us what is the expected output, I would be happy to take a second look.
Active today
I added parens and that did not work - I then propoed "GROUPING SETS" instead
If the output isn't meeting some formatting requirement please show us what the query does produce and what you expect the output to be
SELECT
ISNULL(CAST(A.TopicID AS VARCHAR(2000)), [Report Total:]) AS [Conference]
, ISNULL(A.TopicName, '') AS [Topic Name]
, COUNT(*) AS [Number of Entries]
, SUM(A.Amount) AS Sum
FROM vwApplication A
GROUP BY GROUPING SETS(
(A.TopicID , A.TopicName)
)
If the output isn't meeting some formatting requirement please show us what the query does produce and what you expect the output to be
I tried your proposed solution to use grouping sets, but got a syntax error near SETS. I am using SQL SERVER 9.0.5069.
Featured Post
This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL).
Depending on business requirements, a single Stored Procedure can return differe…
In part one, we reviewed the prerequisites required for installing SQL Server vNext.
In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses
Course of the Month1 day, 19 hours left to enroll
717 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.