Solved

Using aggregate rollup data

Posted on 2012-12-21
16
583 Views
Last Modified: 2013-02-07
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.
0
Comment
Question by:guyriso
  • 7
  • 5
  • 3
  • +1
16 Comments
 
LVL 12

Expert Comment

by:Habib Pourfard
ID: 38713429
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

0
 

Author Comment

by:guyriso
ID: 38745018
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
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 38842622
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

0
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38845665
>>I tried that and I got repeats<<
Define repeats, they look different to me.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38845679
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.
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 334 total points
ID: 38847933
I believe you are seeking something like this:
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

Open in new window


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

Open in new window


Also see: http://msdn.microsoft.com/en-us/library/bb522495(v=sql.105).aspx
0
 

Author Comment

by:guyriso
ID: 38861728
I am getting a syntax error near sets
0
 

Author Comment

by:guyriso
ID: 38861752
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.
0
 

Author Comment

by:guyriso
ID: 38861776
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.
0
 

Author Comment

by:guyriso
ID: 38861794
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
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38862265
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.
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 334 total points
ID: 38862299
I added parens and that did not work - I then propoed "GROUPING SETS" instead
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)
        ) 

Open in new window

If this isn't working, please identify what version of MSSQL you are using.

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
0
 

Author Comment

by:guyriso
ID: 38864067
I tried your proposed solution to use grouping sets, but got a syntax error near SETS.  I am using SQL SERVER 9.0.5069.
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 166 total points
ID: 38864798
>>I am using SQL SERVER 9.0.5069.<<
Correct SQL Server 2005 does not support that syntax.
0
 

Author Closing Comment

by:guyriso
ID: 38865580
Thanks for your help with this.!
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38866360
Thanks for your help with this.!
Ok.   I am not exactly sure how I contributed here, but thanks very much all the same.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

679 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