Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Sum of Sum and TOP (n) 2

Status
Not open for further replies.
LT2

LT2

MIS
May 24, 2006
232
US
Using SQL Svr 2005

How do I get a sum of a sum and then, Top 2?

[tt]
Client Provider Date Code Units PdAmt
Charlie A&W 3/7/2009 123 1 4.00
Charlie A&W 3/8/2009 123 1 4.00
Charlie A&W 3/9/2009 246 1 5.00
Charlie A&W 3/10/2009 555 1 7.50
Woodie B&R 4/1/2009 123 1 4.00
Woodie B&R 4/2/2009 246 1 5.00
Snoops CWW 3/21/2009 123 1 4.00
Snoops CWW 3/22/2009 123 1 4.00
Snoops CWW 3/23/2009 123 1 4.00
Snoops CWW 3/24/2009 123 1 4.00
Snoops CWW 3/25/2009 123 1 4.00
Snoops CWW 3/26/2009 246 1 5.00

[/tt]


My results from sub query:
[tt]
Client TotalPd TotalUnits Provider
Charlie 8.00 2 A&W
Charlie 5.00 1 A&W
Charlie 7.50 1 A&W
Woodie 4.00 1 B&R
Woodie 5.00 1 B&R
Snoops 20.00 5 CWW
Snoops 5.00 1 CWW
[/tt]


What I'd like to see:
[tt]
Client TotalPd TotalUnits Provider
Charlie 20.50 4 A&W
Woodie 9.00 2 B&R
Snoops 25.00 6 CWW
[/tt]


And then the Ultimate result (Top 2 desc)
[tt]
Client TotalPd TotalUnits Provider
Snoops 25.00 6 CWW
Charlie 20.50 4 A&W
[/tt]


Simplified version of my query:
Code: 
SELECT TOP 2 b.Client, b.Cost, b.Units, b.Provider
FROM
(
  Select a.Client, a.ID, sum(a.PdAmt) as 'TotalPd', sum(a.Units) as 'TotalUnits', a.Provider
  From (
         Select c.ClientID as 'ID', (d.LName+ ','+ ' '+ d.FName) as 'Client, p.ProvName as 'Provider',
          t.TxDate, t.TxCode, t.Units, bd.PdAmt
         From tClient c
             inner join tProvider p on c.ProvNum = p.ProvNum
             inner join tTX t on c.ClientNum = t.ClientNum
             left join tBill bd on t.TxID = bd.TxID
         Where p.Test = 0  --No test agency
             and t.TxDate between '3/1/2009' and '4/30/2009'
             and bd.Status = 2 --Paid
             and bd.Adj = 1 --Adjudicated
         )a
   Group by a.Client, a.ID, a.TotalPd, a.TotalUnits, a.Provider
   )b
ORDER BY b.TotalPd desc


Because my results of Table 2 aren't totally summarized like Table 3, my results of TOP 2 are incorrect
as it grabs the largest one summary for the client instead of the largest sum of sum for the client.

How can I fix my syntax to get what I need?

LT
 
Sort by date Sort by votes
; with cte_Initial_Result as (Select a.Client, a.ID, sum(a.PdAmt) as 'TotalPd', sum(a.Units) as 'TotalUnits', a.Provider
From (
Select c.ClientID as 'ID', (d.LName+ ','+ ' '+ d.FName) as 'Client, p.ProvName as 'Provider',
t.TxDate, t.TxCode, t.Units, bd.PdAmt
From tClient c
inner join tProvider p on c.ProvNum = p.ProvNum
inner join tTX t on c.ClientNum = t.ClientNum
left join tBill bd on t.TxID = bd.TxID
Where p.Test = 0 --No test agency
and t.TxDate between '3/1/2009' and '4/30/2009'
and bd.Status = 2 --Paid
and bd.Adj = 1 --Adjudicated
)a
Group by a.Client, a.ID, a.TotalPd, a.TotalUnits, a.Provider)

select * from (select *, Row_Number() over (partition by ClientID ORDER BY SUM(TotalPD) DESC) as RowNum from CTE_Initial_Result) Derived where RowNum <=2

I don't like ORDER BY SUM(TotalPD) here, but according to someone else it's OK

 
Upvote 0 Downvote
Thank you for the prompt reply!

I applied your above suggestion to precede my original Query A with, '; with cte_Initial_Result as'

followed by,
'select * from (select *, Row_Number() over (partition by ClientID ORDER BY SUM(TotalPD) DESC) as RowNum from CTE_Initial_Result) Derived where RowNum <=2'

and get the following error msg:

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "ClientID" could not be bound.

LT


 
Upvote 0 Downvote
I didn't notice that you changed ClientID to ID in your original query. So, use ID instead of ClientID in the code.
 
Upvote 0 Downvote
Sorry 'bout that. I changed to what is in my original query and for these purposes, ID. But now I get the following:

Column 'cte_Initial_Reult.Client' is invalid in the select list because it is not contained in either an aggregate function or the the Group By clause.

LT
 
Upvote 0 Downvote
Does

select * from cte_InitialResult work OK and returns results OK?

I have concerns about ORDER BY SUM(..)

this may not work as we want - may be we need to get the sum into a different derived table first.
 
Upvote 0 Downvote
Yes it appears to render the same results as table 2. I will actually need both table 3 & 4 results but can be separate queries.

I don't know much about cte's but will I be able to use this in a query that uses 'WHERE EXISTS'?

Because, once I've got the TOP (n) working properly, I want another query to pull admission info for clientid's that exist in this query.

Thanks for all your help.

LT
 
Upvote 0 Downvote
With CTE you can only use it in the immediate statement that follows its creation.
 
Upvote 0 Downvote
Okay, so where do we go from here to make the Top(n) work the way I need?

LT
 
Upvote 0 Downvote
I think the problem (with your original query) is that you have too many things in the group by. If you remove the non-aggregate columns, will your query work?

Code: 
SELECT TOP 2 b.Client, b.Cost, b.Units, b.Provider
FROM
(
  Select a.Client, a.ID, sum(a.PdAmt) as 'TotalPd', sum(a.Units) as 'TotalUnits', a.Provider
  From (
         Select c.ClientID as 'ID', 
                (d.LName+ ','+ ' '+ d.FName) as 'Client', 
                p.ProvName as 'Provider',
                t.TxDate, 
                t.TxCode, 
                t.Units, 
                bd.PdAmt
         From tClient c
             inner join tProvider p on c.ProvNum = p.ProvNum
             inner join tTX t on c.ClientNum = t.ClientNum
             left join tBill bd on t.TxID = bd.TxID
         Where p.Test = 0  --No test agency
             and t.TxDate between '3/1/2009' and '4/30/2009'
             and bd.Status = 2 --Paid
             and bd.Adj = 1 --Adjudicated
         )a
   Group by a.Client, a.ID, a.Provider
   )b
ORDER BY b.TotalPd desc

** Note: I removed 2 columns from this line...
Group by a.Client, a.ID, [!]a.TotalPd, a.TotalUnits[/!], a.Provider


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
George, You thought correctly; it was that simple! Big THANKS!

My curiosty though is peaked with the CTE; if we wanted to how would I make that work?

Phase 2. As stated earlier I need to incorporate this query with another to get admit info for those folks that populate the Top(n); basically a parameter.

In theory would you agree the following would work? This is over simplified from actual but... The reason I ask is that I get 8 out of the 10 admit info and 2 strays. I'm doing a Top 10.

Code: 
Select *
From
(
    Select Distinct c.ClientID, a.AdmNum, a.Race, a.Ethnicity, a.Age
    From tProvider p
      Inner join tClient c on p.ProvNum = c.ProvNum
      Inner join tAdmit a on c.ClientNum = a.ClientNum
      Inner join (Select firstB.ClientNum, min(FirstB.BillID) minBill
                  From tBill firstB
                    Inner join (Select ClientNum, min(TxDate) minTxDate
                    	        From tBill b
                    	        Where TxDate between '3/1/2009' and '4/30/2009'
                    	        Group by ClientNum)mb
                    on mb.TxDate = firstB.TxDate and mb.ClientNum = firstB.ClientNum
                   Group by firstB.ClientNum) bd
       on bd.MinBill = a.BillID
     Where P.Test = 0
     ) Admits

WHERE EXISTS
(
SELECT *
FROM
(
    SELECT TOP 2 b.Client, b.Cost, b.Units, b.Provider
    FROM
    (
      Select a.Client, a.ID, sum(a.PdAmt) as 'TotalPd', sum(a.Units) as 'TotalUnits', a.Provider
      From (
             Select c.ClientID as 'ID', 
                    (d.LName+ ','+ ' '+ d.FName) as 'Client', 
                    p.ProvName as 'Provider',
                    t.TxDate, 
                    t.TxCode, 
                    t.Units, 
                    bd.PdAmt
             From tClient c
                 inner join tProvider p on c.ProvNum = p.ProvNum
                 inner join tTX t on c.ClientNum = t.ClientNum
                 left join tBill bd on t.TxID = bd.TxID
             Where p.Test = 0  --No test agency
                 and t.TxDate between '3/1/2009' and '4/30/2009'
                 and bd.Status = 2 --Paid
                 and bd.Adj = 1 --Adjudicated
             )a
       Group by a.Client, a.ID, a.Provider
       )b
    ORDER BY b.TotalPd desc
    ) Tx

WHERE Admits.ID = Tx.ClientID
)

LT
 
Upvote 0 Downvote
I'm glad I was able to help.

Truth is.... Markros is better at CTE than I am. Hopefully Markros (or someone else) will chime in with further advice.

I would probably have written your query differently. I don't know if it will make a difference or not, but I would have written it this way...

Code: 
Select *
From  (
      Select Distinct c.ClientID, a.AdmNum, a.Race, a.Ethnicity, a.Age
      From tProvider p
        Inner join tClient c on p.ProvNum = c.ProvNum
        Inner join tAdmit a on c.ClientNum = a.ClientNum
        Inner join (Select firstB.ClientNum, min(FirstB.BillID) minBill
                    From tBill firstB
                      Inner join (Select ClientNum, min(TxDate) minTxDate
                                  From tBill b
                                  Where TxDate between '3/1/2009' and '4/30/2009'
                                  Group by ClientNum)mb
                      on mb.TxDate = firstB.TxDate and mb.ClientNum = firstB.ClientNum
                     Group by firstB.ClientNum) bd
         on bd.MinBill = a.BillID
       Where P.Test = 0
       ) Admits
     Inner Join 
(
    SELECT TOP 2 b.Client, b.Cost, b.Units, b.Provider
    FROM
    (
      Select a.Client, a.ID, sum(a.PdAmt) as 'TotalPd', sum(a.Units) as 'TotalUnits', a.Provider
      From (
             Select c.ClientID as 'ID',
                    (d.LName+ ','+ ' '+ d.FName) as 'Client',
                    p.ProvName as 'Provider',
                    t.TxDate,
                    t.TxCode,
                    t.Units,
                    bd.PdAmt
             From tClient c
                 inner join tProvider p on c.ProvNum = p.ProvNum
                 inner join tTX t on c.ClientNum = t.ClientNum
                 left join tBill bd on t.TxID = bd.TxID
             Where p.Test = 0  --No test agency
                 and t.TxDate between '3/1/2009' and '4/30/2009'
                 and bd.Status = 2 --Paid
                 and bd.Adj = 1 --Adjudicated
             )a
       Group by a.Client, a.ID, a.Provider
       )b
    ORDER BY b.TotalPd desc
    ) Tx
    On Admits.ID = Tx.ClientID

Basically.... I would have joined to the query as a derived table joining on the ClientId column. This should effectively return the same data.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
I'm not better, I just hang up on the other forum a bit longer and learned a few things from Rami & limno :)

As for the query, it's too complex for me to grasp it now, I'm not sure I understood what was the intent of it.
 
Upvote 0 Downvote
That's two for you George! That made the difference and gives me exactly what I need, so Thank YOU.

I still would like to widen my horizon by learning how we would make the CTE work for the Top(n) Markros. Any further advice?

LT
 
Upvote 0 Downvote
I still would like to widen my horizon by learning how we would make the CTE work for the Top(n) Markros.
Click to expand...

Me too. Once my customers let me move away from SQL 2000. [sad]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
I did find an article on SQL SVR 2005 Common Table Expressions by Nigel Rivett of Simple-Talk that explains them.

I'm looking for how you would make my query work for the Top(n) using a CTE. You commented you don't care for the, "Order By" portion so how would you approach it?

LT
 
Upvote 0 Downvote
Ok, I don't exactly understand what was going on, so I will just re-write George's query (give it slightly more modern look :))

;with cte_Admits as
( Select Distinct c.ClientID, a.AdmNum, a.Race, a.Ethnicity, a.Age
From tProvider p
Inner join tClient c on p.ProvNum = c.ProvNum
Inner join tAdmit a on c.ClientNum = a.ClientNum
Inner join (Select firstB.ClientNum, min(FirstB.BillID) minBill
From tBill firstB
Inner join (Select ClientNum, min(TxDate) minTxDate
From tBill b
Where TxDate between '3/1/2009' and '4/30/2009'
Group by ClientNum)mb
on mb.TxDate = firstB.TxDate and mb.ClientNum = firstB.ClientNum
Group by firstB.ClientNum) bd
on bd.MinBill = a.BillID
Where P.Test = 0
),
Other_cte as (
Select a.Client, a.ID, TotalPd, TotalUnits,
a.Provider
From (
Select c.ClientID as 'ID',
(d.LName+ ','+ ' '+ d.FName) as 'Client',
p.ProvName as 'Provider',
t.TxDate,
t.TxCode,
t.Units,
bd.PdAmt,
row_number() OVER (partition by c.ClientID, p.ProvName order by SUM(a.PdAmt) DESC) as rank, sum(Units) over (partition by c.ClientID, p.ProvName) as TotalUnits, same for TotalPd
From tClient c
inner join tProvider p on c.ProvNum = p.ProvNum
inner join tTX t on c.ClientNum = t.ClientNum
left join tBill bd on t.TxID = bd.TxID
Where p.Test = 0 --No test agency
and t.TxDate between '3/1/2009' and '4/30/2009'
and bd.Status = 2 --Paid
and bd.Adj = 1 --Adjudicated
)a where a.Rank <=2
)


Select * from cte_Admits Admits
Inner Join
OtherCte Tx
On Admits.ID = Tx.ClientID

Obviously from the top of my head and not tested, besides I'm still concerned for including SUM() in Order BY.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

grnzbra
  • Locked
  • Question
SELECT TOP 1 of Each of a Group
Replies
5
Views
458
grnzbra
grnzbra
agorjest1
  • Locked
  • Question
SQL Conditional Sum
Replies
0
Views
223
agorjest1
agorjest1
peac3
  • Locked
  • Question
fill the gap of hierarchy data
Replies
11
Views
881
Chris Miller
Chris Miller
peac3
  • Locked
  • Question
Gaps and islands
Replies
8
Views
483
Chris Miller
Chris Miller
grnzbra
  • Locked
  • Question
How Do I Add Column to Middle of Table
Replies
4
Views
767
BobSuruncle
BobSuruncle

Part and Inventory Search

Sponsor

Back
Top