Solved

SQL - Sum of the Column Value - Agg function

Posted on 2011-02-16
27
1,026 Views
Last Modified: 2012-06-27
SQL 2008

To do :- Sum of Column Value

I have attached the sample output.  In the Sample Output, we can see 4 Columns :- GenericCode, NDC, DrugName, Qty.

I need the result by Sum ( Generic Code ) by picking the NDC / DrugName for the Maximum Qty.

For example :- In the Sample Output, we have NDC # 64679072002 , Which has maximum Qty : 450.

And other Generic Code has Qty : 30.

So, expected output should be
4521    64679072002          PHENYTOIN SOD EXT 100 MG CA          480                     


select    
   TR.[Generic Code],  
   TR.NDC,     
   TR.[Drug Name],
   Sum(TR.[Dispensed Qty]) as Qty  
from  TransactionReport TR      
Group By     TR.[Generic Code],TR.NDC,TR.[Drug Name]

Open in new window

Output.txt
0
Comment
Question by:chokka
  • 10
  • 9
  • 7
  • +1
27 Comments
 
LVL 11

Expert Comment

by:David Kroll
ID: 34911069
select   top 1
   TR.[Generic Code],  
   TR.NDC,    
   TR.[Drug Name],
   (select Sum(TR.[Dispensed Qty]) from TransactionReport TR2 where TR2.[Generic Code] = TR.[Generic Code]) as Qty  
from  TransactionReport TR      
Group By     TR.[Generic Code],TR.NDC,TR.[Drug Name]

This will still give you two rows of results.  If the NDC and Drug Name columns had the same value, you could use select distinct.  In this case, try using select top 1.
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 34911147
try this:

with p as(
select    
   TR.[Generic Code],  
   TR.NDC,    
   TR.[Drug Name],
   Sum(TR.[Dispensed Qty]) as Qty  
from  TransactionReport TR      
Group By     TR.[Generic Code],TR.NDC,TR.[Drug Name]
)
select * from
(
select p.*, row_number() over (partition by [Generic Code] order by Qty desc) from p
) x where rn=1;
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 34911156
I forgot alias name rn for row_number :)

with p as(
select    
   TR.[Generic Code],  
   TR.NDC,    
   TR.[Drug Name],
   Sum(TR.[Dispensed Qty]) as Qty  
from  TransactionReport TR      
Group By     TR.[Generic Code],TR.NDC,TR.[Drug Name]
)
select * from
(
select p.*, row_number() over (partition by [Generic Code] order by Qty desc) rn from p
) x where rn=1;
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Comment

by:chokka
ID: 34911164

select
                   NDC ,
                  Sum(CONVERT(INT, Qty)) as Qty from  Table  
Group By      NDC

What if i am writing a simple query like this .. To this query, if i need to choose the NDC of its highest Qty field.

I can fetch DrugName from other table once i got the NDC
0
 

Author Comment

by:chokka
ID: 34911193
Hainkurt, i am just working on your query - and trying to find out the value outputs
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 34911201
then use this
Select  [Generic Code],  
   NDC,     
   [Drug Name],
   qty
from (
select    
   TR.[Generic Code],  
   TR.NDC,     
   TR.[Drug Name],
   Sum(TR.[Dispensed Qty]) over (partition by [generic code] order by [generic code]) as Qty  
   ,row_number() over (partition by [generic code] order by [dispensed qty] desc) as rn
from  TransactionReport TR      
) as x
where rn=1
order by 1

Open in new window

0
 

Author Comment

by:chokka
ID: 34911230
Lowfat spread, there is kind of syntax error ..

Msg 102, Level 15, State 1, Line 10
Incorrect syntax near 'order'.
0
 

Author Comment

by:chokka
ID: 34911242
Hainkurt :- In your query, there is a column name " rn" - which pops up .

I need to hide that column while we are executing the SELECT statement.

How should i do this ?
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 34911259
modify query as:

select * from
-->
select [Generic Code], NDC, [Drug Name], Qty from
0
 

Author Comment

by:chokka
ID: 34911291
Hainkurt, i request to help me by remodifying the sql.
with p as(
select    
   TR.[Generic Code],  
   TR.NDC,     
   TR.[Drug Name],
   Sum(TR.[Dispensed Qty]) as Qty  
from  TransactionReport TR      
Group By     TR.[Generic Code],TR.NDC,TR.[Drug Name] 
)
select * from 
(
select p.GenericCode,p.NDC,p.[Drug Name],p.Qty
 
from p
) x 
where rn=1; 



--row_number() over (partition by [Generic Code] order by Qty desc) rn

Open in new window

0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 34911930
Select  [Generic Code],  
   NDC,    
   [Drug Name],
   qty
from (
select    
   TR.[Generic Code],  
   TR.NDC,    
   TR.[Drug Name],
   Sum(TR.[Dispensed Qty]) over (partition by [generic code]) as Qty  
   ,row_number() over (partition by [generic code] order by [dispensed qty] desc) as rn
from  TransactionReport TR      
) as x
where rn=1
order by 1
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 34911960
hainkurts query will not provide you with what you desire as its still grouping by    TR.[Generic Code],TR.NDC,TR.[Drug Name]


all the columns whilst you have stated you wish to group to generic code only.
0
 

Author Comment

by:chokka
ID: 34912238
I agree with you .. but i need to test the query
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 34912952
here how to remove rn from resultset...
is there any issue with this query? it is based on your original query... it is just grouping by [Generic Code] and ordering them by Qty desc then we filter the result and only get top records for each  [Generic Code].
with p as(
select    
   TR.[Generic Code],  
   TR.NDC,     
   TR.[Drug Name],
   Sum(TR.[Dispensed Qty]) as Qty  
from  TransactionReport TR      
Group By TR.[Generic Code], TR.NDC,TR.[Drug Name] 
)
select [Generic Code], NDC, [Drug Name], Qty from 
(
select p.*, row_number() over (partition by [Generic Code] order by Qty desc) rn from p
) x where rn=1;

Open in new window

0
 
LVL 51

Expert Comment

by:HainKurt
ID: 34912972
if we talk on a sample
lets say this is the result set from "with p as (...) query"

rn	Generic Code		NDC			Drug Name					Qty
1	4521			64679072002    		PHENYTOIN SOD EXT 100 MG CA			450
2	4521			00378156001    		PHENYTOIN SOD EXT 100MG CAP			30
3	4521			64679072002    		PHENYTOIN SOD EXT 100 MG CA			12
1	4556			64679072003    		PHENYTOIN SOD EXT 100 MG CA 1			150
2	4556			64679072005    		PHENYTOIN SOD EXT 100 MG CA 2			26
1	4558			64679072007    		PHENYTOIN SOD EXT 100 MG CA 3			78
2	4558			64679072008    		PHENYTOIN SOD EXT 100 MG CA 4			6

then we get only the records with rn=1 which is

rn	Generic Code		NDC			Drug Name					Qty
1	4521			64679072002    		PHENYTOIN SOD EXT 100 MG CA			450
1	4556			64679072003    		PHENYTOIN SOD EXT 100 MG CA 1			150
1	4558			64679072007    		PHENYTOIN SOD EXT 100 MG CA 3			78

Open in new window

0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 34912981
@hainkurt

line 8

Group By TR.[Generic Code], TR.NDC,TR.[Drug Name]
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 34913009
if you look at the first sample he posted, the query is giving this result

Generic Code            NDC                  Drug Name                  Qty
4521                  00378156001                PHENYTOIN SOD EXT 100MG CAP      30
4521                  64679072002                PHENYTOIN SOD EXT 100 MG CA      450

and he wants only second row since qty=450
so, based on this I created another sample for multiple Codes
and the query I posted should work on this... looks like he has multiple record per [Generic Code]

maybe I missed the point here, I will check the posts again...

maybe he should post a sample data from the table for multiple [Generic Code] and a a resultset that he is looking for based on the sample data...

0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 34913013
no he says he wants the NDC and Drug from line 2 and the sum of the quantity   480 not 450...
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 34913017
if he groups by only [Generic Code] then there wont be any multiple record per code, and we will not have any issue :)

select [Generic Code], sum(qty) qty
from myTable
group by [Generic Code]

will do the trick...
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 34913023
based on this

Generic Code            NDC                  Drug Name                  Qty
4521                  00378156001                PHENYTOIN SOD EXT 100MG CAP      30
4521                  64679072002                PHENYTOIN SOD EXT 100 MG CA      450

does he want:

Generic Code            NDC                  Drug Name                  Qty
4521                  64679072002                PHENYTOIN SOD EXT 100 MG CA      480

???
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 34913041
:) I guess I got it finally...

what about this, yes it looks ugly...
with p as(
select    
   TR.[Generic Code],  
   TR.NDC,     
   TR.[Drug Name],
   Sum(TR.[Dispensed Qty]) as Qty  
from  TransactionReport TR      
Group By TR.[Generic Code], TR.NDC,TR.[Drug Name] 
)
select x.[Generic Code], x.NDC, x.[Drug Name], y.Qty from 
(
select p.*, row_number() over (partition by [Generic Code] order by Qty desc) rn from p
) x left join (select [Generic Code], sum(Qty) Qty from TransactionReport group by [Generic Code]) y on x.[Generic Code]=y.[Generic Code]
where rn=1;

Open in new window

0
 
LVL 51

Accepted Solution

by:
HainKurt earned 500 total points
ID: 34913052
and this is better since we use p instead of original table...
with p as (
select    
   TR.[Generic Code],  
   TR.NDC,     
   TR.[Drug Name],
   Sum(TR.[Dispensed Qty]) as Qty  
from  TransactionReport TR      
Group By TR.[Generic Code], TR.NDC,TR.[Drug Name] 
)
select x.[Generic Code], x.NDC, x.[Drug Name], y.Qty from 
(
select p.*, row_number() over (partition by [Generic Code] order by Qty desc) rn from p
) x 
left join 
(
select [Generic Code], sum(Qty) Qty from p group by [Generic Code]
) y 
on x.[Generic Code]=y.[Generic Code]
where rn=1;

Open in new window

0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 34913149
@hainkurt ;-( not necessarily... he said he wanted the data from the row with the max value ...
so you can't do the intermediate sum and guarantee that....

and anyway the "Best" solution would depend on the actual size and organisation of his base table , dp no
i don't think you can generalise that using P would be inherently "better"
0
 

Author Comment

by:chokka
ID: 34916797
Comment  ID: 34913023  - Hainkurt , Yes i want it as what you mentioned.
0
 

Author Closing Comment

by:chokka
ID: 34916828
Thank you
0
 

Author Comment

by:chokka
ID: 34917723
I have a sequence of this posting  to next level which i posted over here

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/Q_26828620.html#a34917680

Can you able to help me ..!
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 34920458
can i know what was in error with my solution please?

ID:34911930
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

765 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