Link to home
Start Free TrialLog in
Avatar of guyriso
guyriso

asked on

Using aggregate rollup data

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.
Avatar of Habib Pourfard
Habib Pourfard
Flag of New Zealand image

inluce topic name in the group by:

GROUP BY A.TopicID, A.TopicName

Open in new window

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

Open in new window

Avatar of guyriso
guyriso

ASKER

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
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

Open in new window

>>I tried that and I got repeats<<
Define repeats, they look different to me.
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.
ASKER CERTIFIED SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of guyriso

ASKER

I am getting a syntax error near sets
Avatar of guyriso

ASKER

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.
Avatar of guyriso

ASKER

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.
Avatar of guyriso

ASKER

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.
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.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of guyriso

ASKER

I tried your proposed solution to use grouping sets, but got a syntax error near SETS.  I am using SQL SERVER 9.0.5069.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of guyriso

ASKER

Thanks for your help with this.!
Thanks for your help with this.!
Ok.   I am not exactly sure how I contributed here, but thanks very much all the same.