CODEWITHSUNDEEP
sql-server
Gopal Krishnan Iyer
Gopal Krishnan Iyer

Reputation: 1

SQL summary queries

I have an output from a SQL query. I want to refine the results to make it more meaningful.

The query returns the following results

Name    Date    Measure No    Measure Value    Measure Percent
--------------------------------------------------------------
xyz     30/06/18    1           33857               18
xyz     30/06/18    2           11056               45
xyz     30/06/18    4           46487               28
xyz     30/06/18    5           34376               48
abc     30/06/18    1           24982               27
abc     30/06/18    2           43234               45
abc     30/06/18    4           13791               25
abc     30/06/18    5           22261               20
lmn     30/06/18    1           45728               41
lmn     30/06/18    2           31480               48
lmn     30/06/18    4           32750               26
lmn     30/06/18    5           44771               36

I want to summarize further. I want to add the result of Measure 1 and Measure 2 to a temporary measure 3 for a particular name and date. The result of the query should be like this

Name    Date    Measure No  Measure Value   Measure Percent
-----------------------------------------------------------
xyz 30/06/18    1   33857   18
xyz 30/06/18    2   11056   45
xyz 30/06/18    3   44913   63
xyz 30/06/18    4   46487   28
xyz 30/06/18    5   34376   48
abc 30/06/18    1   24982   27
abc 30/06/18    2   43234   45
abc 30/06/18    3   68216   72
abc 30/06/18    4   13791   25
abc 30/06/18    5   22261   20
lmn 30/06/18    1   45728   41
lmn 30/06/18    2   31480   48
lmn 30/06/18    3   77208   89
lmn 30/06/18    4   32750   26
lmn 30/06/18    5   44771   36

How can I do this?

Upvotes: 0

Views: 67

Answers (2)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

you may need union or union all based on your output 

select col1,col2.. from your 1stQueryoutput
union 

select col1,col2.. from your 2ndQueryoutput

or

select col1,col2.. from your 1stQueryoutput
union all

select col1,col2.. from your 2ndQueryoutput

Upvotes: 0

Wolfgang Kais
Wolfgang Kais

Reputation: 4100

If you just want to add rows with the new Measure No 3 as the sum of values and percentages of 1 and 2 by Name and Date, then this should do it for you:

SELECT [Name], [Date], [Measure No], [Measure Value], [Measure Percent]
FROM YourQueryResult

UNION ALL

SELECT [Name], [Date], 3, SUM([Measure Value]), SUM([Measure Percent])
FROM YourQueryResult
WHERE [Measure No] IN (1, 2)
GROUP BY [Name], [Date]

Upvotes: 1

Related Questions