SQL - Sum of the Column Value - Agg function

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.

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;

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;

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

Hainkurt, i am just working on your query - and trying to find out the value outputs

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

Select allOpen in new window

Lowfat spread, there is kind of syntax error ..

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

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 ?

modify query as:

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

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

Select allOpen in new window

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

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.

I agree with you .. but i need to test the query

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;

Select allOpen in new window

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

Select allOpen in new window

@hainkurt

line 8

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

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

no he says he wants the NDC and Drug from line 2 and the sum of the quantity   480 not 450...

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

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

???

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

Select allOpen in new window

@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"

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

Thank you

I have a sequence of this posting  to next level which i posted over here

https://www.experts-exchange.com/questions/26828620/SQL-Sum-of-the-Column-Value-Agg-function-By-Combining-two-different-Queries.html?anchorAnswerId=34917680#a34917680

Can you able to help me ..!

can i know what was in error with my solution please?

ID:34911930