Solved

SQL - Sum of the Column Value - Agg function

Posted on 2011-02-16
27
1,016 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:chokka
Comment Utility

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
Comment Utility
Hainkurt, i am just working on your query - and trying to find out the value outputs
0
 
LVL 50

Expert Comment

by:Lowfatspread
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
modify query as:

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

Author Comment

by:chokka
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
I agree with you .. but i need to test the query
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 51

Expert Comment

by:HainKurt
Comment Utility
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
Comment Utility
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
Comment Utility
@hainkurt

line 8

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

Expert Comment

by:HainKurt
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
:) 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
Comment Utility
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
Comment Utility
@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
Comment Utility
Comment  ID: 34913023  - Hainkurt , Yes i want it as what you mentioned.
0
 

Author Closing Comment

by:chokka
Comment Utility
Thank you
0
 

Author Comment

by:chokka
Comment Utility
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
Comment Utility
can i know what was in error with my solution please?

ID:34911930
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This video discusses moving either the default database or any database to a new volume.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

728 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now