Solved

# SQL - Sum of the Column Value - Agg function

Posted on 2011-02-16
1,024 Views
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]
``````
Output.txt
0
Question by:chokka
• 10
• 9
• 7
• +1

LVL 11

Expert Comment

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

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

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

Author Comment

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

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

LVL 50

Expert Comment

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
``````
0

Author Comment

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

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

ID: 34911259
modify query as:

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

Author Comment

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
``````
0

LVL 50

Expert Comment

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

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

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

LVL 51

Expert Comment

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;
``````
0

LVL 51

Expert Comment

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
``````
0

LVL 50

Expert Comment

ID: 34912981
@hainkurt

line 8

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

LVL 51

Expert Comment

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

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

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

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

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

``````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;
``````
0

LVL 51

Accepted Solution

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;
``````
0

LVL 50

Expert Comment

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

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

Author Closing Comment

ID: 34916828
Thank you
0

Author Comment

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

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

ID:34911930
0

## Featured Post

Question has a verified solution.

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

### Suggested Solutions

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common â€¦
SQL Server engine let youÂ use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties â€¦
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designedâ€¦
Simple Linear Regression