[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1039
  • Last Modified:

SQL - Sum of the Column Value - Agg function

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
chokka
Asked:
chokka
  • 10
  • 9
  • 7
  • +1
1 Solution
 
David KrollCommented:
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
 
HainKurtSr. System AnalystCommented:
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
 
HainKurtSr. System AnalystCommented:
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.

 
chokkaAuthor Commented:

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
 
chokkaAuthor Commented:
Hainkurt, i am just working on your query - and trying to find out the value outputs
0
 
LowfatspreadCommented:
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
 
chokkaAuthor Commented:
Lowfat spread, there is kind of syntax error ..

Msg 102, Level 15, State 1, Line 10
Incorrect syntax near 'order'.
0
 
chokkaAuthor Commented:
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
 
HainKurtSr. System AnalystCommented:
modify query as:

select * from
-->
select [Generic Code], NDC, [Drug Name], Qty from
0
 
chokkaAuthor Commented:
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
 
LowfatspreadCommented:
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
 
LowfatspreadCommented:
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
 
chokkaAuthor Commented:
I agree with you .. but i need to test the query
0
 
HainKurtSr. System AnalystCommented:
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
 
HainKurtSr. System AnalystCommented:
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
 
LowfatspreadCommented:
@hainkurt

line 8

Group By TR.[Generic Code], TR.NDC,TR.[Drug Name]
0
 
HainKurtSr. System AnalystCommented:
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
 
LowfatspreadCommented:
no he says he wants the NDC and Drug from line 2 and the sum of the quantity   480 not 450...
0
 
HainKurtSr. System AnalystCommented:
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
 
HainKurtSr. System AnalystCommented:
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
 
HainKurtSr. System AnalystCommented:
:) 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
 
HainKurtSr. System AnalystCommented:
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
 
LowfatspreadCommented:
@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
 
chokkaAuthor Commented:
Comment  ID: 34913023  - Hainkurt , Yes i want it as what you mentioned.
0
 
chokkaAuthor Commented:
Thank you
0
 
chokkaAuthor Commented:
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
 
LowfatspreadCommented:
can i know what was in error with my solution please?

ID:34911930
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 10
  • 9
  • 7
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now