chokka
asked on
Sum of Agg Function - SQL Query.
SQL 2008
Question Sequence : https://www.experts-exchange.com/questions/26826746/SQL-Sum-of-the-Column-Value-Agg-function.html
I need to SUM of Qty Column which is Group by GenericCode Column. Value of NDC & DrugName has to pick from Default Largest Qty Column. To do this, i have attached SQL Query which thought to do this feature. But it failed.
I have attached an Excel Sheet - SampleDate.xls
In which Tab : CurrentDatabase - which is directly extracted from database by the query
select * from vw_Test3
From the Tab : DerivedFromQuery - is the output which i got from the attached Query.
Tab - SampleExpectedData :- Explains what we expect from the Query.
Question Sequence : https://www.experts-exchange.com/questions/26826746/SQL-Sum-of-the-Column-Value-Agg-function.html
I need to SUM of Qty Column which is Group by GenericCode Column. Value of NDC & DrugName has to pick from Default Largest Qty Column. To do this, i have attached SQL Query which thought to do this feature. But it failed.
I have attached an Excel Sheet - SampleDate.xls
In which Tab : CurrentDatabase - which is directly extracted from database by the query
select * from vw_Test3
From the Tab : DerivedFromQuery - is the output which i got from the attached Query.
Tab - SampleExpectedData :- Explains what we expect from the Query.
with p as (
select
TR.[Generic Code],
TR.NDC,
TR.[Drug Name],
Sum(cast(TR.[Qty] as int)) as Qty
from vw_Test3 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(cast(Qty as int)) Qty from p group by [Generic Code]
) y
on x.[Generic Code]=y.[Generic Code]
where rn=1;
SampleData.xls
ASKER
Thanks
If you seen the Excel Sheet Tab : Derived from Query - I have 251 rows affected.
Your Query, and the Query which i attached results the same number of rows.
Query doesn't give exact results. It is ignoring .. NULL Values in the
GenericCode,DrugName Columns
If you seen the Excel Sheet Tab : Derived from Query - I have 251 rows affected.
Your Query, and the Query which i attached results the same number of rows.
Query doesn't give exact results. It is ignoring .. NULL Values in the
GenericCode,DrugName Columns
ASKER
Sharath123 :- I am able to find the issue.
In my Query, is there anyway to add a Condition by mentioning
IF GenericCode Column is NULL , Ignore these Steps.
In my Query, is there anyway to add a Condition by mentioning
IF GenericCode Column is NULL , Ignore these Steps.
Add a filter on Generic Code is NOT NULL.
SELECT *
FROM (SELECT *,
SUM(Qty)
OVER(PARTITION BY [Generic Code] ) sum_Qty,
ROW_NUMBER()
OVER(PARTITION BY [Generic Code] ORDER BY Qty) rn
FROM vw_Test3
WHERE [Generic Code] IS NOT NULL) t1
WHERE rn = 1
ASKER
Is there anyway for me to merge these two SQL Query - by using UNION ALL Keyword.
I am aware by keeping the first set of SQL QUery into another View and then go for Union All .
Too many views affects the performance of SQL ..!
I am aware by keeping the first set of SQL QUery into another View and then go for Union All .
Too many views affects the performance of SQL ..!
with p as (
select
TR.[Generic Code],
TR.NDC,
TR.[Drug Name],
Sum(cast(TR.[Qty] as int)) as Qty
from vw_Test3 TR
where TR.[Generic Code] is not null
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(cast(Qty as int)) Qty from p
where [Generic Code] is not null
group by [Generic Code]
) y
on x.[Generic Code]=y.[Generic Code]
where rn=1;
--UNION ALL
select * from vw_Test3 where [Generic Code] IS NULL
Did you see any issue with my suggested query? I don't understand why you want to query the table multiple times when you can get the result in a straight way.
ASKER
In your suggested query,
Number of rows affected are correct. But Values which summed in Qty is not perfect.
I feel hard to check row by row to find the issues .. but there is mismatch on the records what we expect and what it generated.
Number of rows affected are correct. But Values which summed in Qty is not perfect.
I feel hard to check row by row to find the issues .. but there is mismatch on the records what we expect and what it generated.
You want the sum of Qty for every Generic Code.
You want to display the record with max Qty for every Generic Code.
You don't want NULL Generic Code records.
Is my understanding correct?
You want to display the record with max Qty for every Generic Code.
You don't want NULL Generic Code records.
Is my understanding correct?
ASKER
Yes sir...
For Example :-
Generic Code NDC Drug Name Qty
392 68180051703 LISINOPRIL 40 MG TABLET 60
392 64679094201 LISINOPRIL 40 MG TABLET 30
392 64679094201 LISINOPRIL 40 MG TABLET 30
Expected output :-
392 68180051703 LISINOPRIL 40 MG TABLET 120
In the Expected Output, You can see , we cumulated with Maximum Qty listed GenericCode / DrugName.
For Example :-
Generic Code NDC Drug Name Qty
392 68180051703 LISINOPRIL 40 MG TABLET 60
392 64679094201 LISINOPRIL 40 MG TABLET 30
392 64679094201 LISINOPRIL 40 MG TABLET 30
Expected output :-
392 68180051703 LISINOPRIL 40 MG TABLET 120
In the Expected Output, You can see , we cumulated with Maximum Qty listed GenericCode / DrugName.
Can you post at least one such mismatched Qty record with my query?
ASKER
I am checking row by row .. few issues are mismatching with NDC
Generic Code NDC Drug Name Qty sum_Qty rn
393 64679092801 LISINOPRIL 5 MG TABLET 30 120 1
Generic Code NDC Drug Name Qty sum_Qty rn
1775 65862003001 GLYBURIDE 5 MG TABLET 30 30 1
Generic Code NDC Drug Name Qty sum_Qty rn
393 64679092801 LISINOPRIL 5 MG TABLET 30 120 1
Generic Code NDC Drug Name Qty sum_Qty rn
1775 65862003001 GLYBURIDE 5 MG TABLET 30 30 1
Can you post the records from your view vw_Test3 for Generic Code 393?
ASKER
Actually both query output has issues ..! I have taken the output in an excel sheet and trying to find out duplicate records.
My bad. I missed the DESC keyword. try this.
SELECT *
FROM (SELECT *,
SUM(Qty)
OVER(PARTITION BY [Generic Code] ) sum_Qty,
ROW_NUMBER()
OVER(PARTITION BY [Generic Code] ORDER BY Qty desc) rn
FROM CurrentDatabase
WHERE [Generic Code] IS NOT NULL) t1
ASKER
Sharath
SELECT *
FROM (SELECT *,
SUM(Qty)
OVER(PARTITION BY [Generic Code] ) sum_Qty,
ROW_NUMBER()
OVER(PARTITION BY [Generic Code] ORDER BY Qty desc) rn
FROM vw_Test3
WHERE [Generic Code] IS NOT NULL) t1
This query returns 344 Rows. Which is completely wrong.
SELECT *
FROM (SELECT *,
SUM(Qty)
OVER(PARTITION BY [Generic Code] ) sum_Qty,
ROW_NUMBER()
OVER(PARTITION BY [Generic Code] ORDER BY Qty desc) rn
FROM vw_Test3
WHERE [Generic Code] IS NOT NULL) t1
This query returns 344 Rows. Which is completely wrong.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
i just missed it, will check it and close this question.
Open in new window