I wrote some Sql and what I got is as follows:
| Name | Rev | Cost | Qty |
|---|---|---|---|
| X | 1000 | NULL | |
| X | NULL | 100 | |
| X | NULL | NULL | 3 |
| Z | NULL | NULL | 8 |
| Z | 300 | NULL | |
| Z | NULL | 80 |
What I want to achieve is one line with values for each Name, here is the desired ouput:
| Name | Rev | Cost | Qty |
|---|---|---|---|
| X | 1000 | 100 | 3 |
| Z | 300 | 80 | 8 |
I tried to combine GROUP BY with WHERE (with IS NOT NULL for columns) and I know it doesn't work but have no other idea how to solve it. I think I need some special type of grouping but don't know how to do this.
SELECT Name, Rev, Cost, Qty
FROM #table
WHERE Rev IS NOT NULL OR Cost IS NOT NULL OR Qty IS NOT NULL
GROUP BY Name, Rev, Cost, Qty
CodePudding user response：
I have solved your question. Use the following query to get the desired output.
SELECT Name, SUM(Rev) as Rev, SUM(Cost) as Cost, SUM(Qty) as Qty FROM mustafa GROUP BY Name
NOTE: the table name I used is mustafa
Since the other columns are numeric values, using SUM will get the total whiles the GROUP BY will get you the names.