CODEWITHSUNDEEP
mysqlsql
Alex Verzea
Alex Verzea

Reputation: 73

Combining entries in SQL

I am not sure how to do the following in SQL. I tried using GROUP BY and HAVING in different ways, but none works.

I have a table like this:

Title   Value 1   Value 2 ...
0       3         7
1       4         8
2       5         9
(empty) 6         10
0       1         2

Here, (empty) is not a string. I just put it there to show that that table cell is empty(null). I want to display this table with only one change. All rows with Title 0 or (empty) are "combined" into a single row, with Title 0 and Value 1 = Sum(all Value 1s), Value 2 = Sum(all Value 2s). Everything else stays the same. So, in the example above, I want to see:

Title   Value 1   Value 2 ...  
0       10        19
1       4         8
2       5         9

Can anyone please help?

Thank you!

Upvotes: 2

Views: 76

Answers (3)

1000111
1000111

Reputation: 13519

You can try this:

SELECT 
IF(Title ='(empty)',0,Title) AS title_col,
SUM(value1) as Value_1,
SUM(value2) as Value_2
FROM your_table
GROUP BY title_col;

Note:

If the Title column actually holds this string (empty) then you can go with this query.

EDIT:

If the datatype of the column is INT then it can have null value if it's declared as ALLOW NULL.

If so then the query would be modified like below:

SELECT 
IF(Title IS NULL,0,Title) AS title_col,
SUM(value1) as Value_1,
SUM(value2) as Value_2
FROM your_table
GROUP BY title_col;

Upvotes: 2

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

I see two queries, one for title records and one for the aggregation of the non-title records. Use UNION ALL to glue the two results together:

select title, value1, value2
from my table where coalesce(title, 0) <> 0
union all
select 0, sum(value1), sum(value2)
from my table where coalesce(title, 0) = 0
group by title
order by title;

(In case that all title <> 0 were unique in the table, you could use one single query with group by coalesce(title, 0) instead.)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269603

This is an aggregation query with a case:

select (case when title is null or title = 0 then 0 else title end) as title,
       sum(value1) as value1, sum(value2) as value2
from t
group by (case when title is null or title = 0 then 0 else title end)
order by title;

If you like, you could also use the logic:

select coalesce(title, 0) as title,
       sum(value1) as value1, sum(value2) as value2
from t
group by coalesce(title, 0)
order by title;

Upvotes: 3

Related Questions