CODEWITHSUNDEEP
sqlsql-serverselect
mhn
mhn

Reputation: 2750

select statement to always return 3 rows on execution

i have a select statement which always returns only 3 rows. One column has static values RED AMBER and GREEN.

http://sqlfiddle.com/#!6/5e107/2

In the fiddle link, i have a sample code.

 select color,SUM(clrcount) from 
 tmp group by color

returns 3 rows on execution. But if i delete items marked AMBER, and then do a group by, only two rows are returned. I need 3 rows to be always returned. The AMBER row can have count as 0

Upvotes: 1

Views: 296

Answers (5)

satyajit
satyajit

Reputation: 2700

select color,SUM(clrcount) from 
 (SELECT * from tmp 
 UNION ALL 
 SELECT 'RED',0
 UNION ALL
 SELECT 'AMBER',0
 UNION ALL
 SELECT 'GREEN',0)temp
 group by color

Upvotes: 1

Khan
Khan

Reputation: 18162

To use the schema present in your question, you should be able to do the following:

SELECT color, MAX(clrcount) AS clrcount
FROM (
    SELECT color, COUNT(clrcount) AS clrcount
    FROM tmp T
    GROUP BY color

    UNION ALL SELECT 'RED', 0
    UNION ALL SELECT 'AMBER', 0
    UNION ALL SELECT 'GREEN', 0
) A 
GROUP BY color

Use a UNION to ensure the rows you want are present in your results.

Upvotes: 1

Kaf
Kaf

Reputation: 33829

Redesigning would be a better approach but if you can't do it now and certain about those three colours, try something like this :

Fiddle demo

select t.color,coalesce(sum(clrcount),0) colorCount
from tmp  right join 
         (values('RED'),('AMBER'),('GREEN')) As t(color)
     on tmp.color = t.color
group by t.color

Upvotes: 4

Chris L
Chris L

Reputation: 2292

Rather than deleting amber rows you need to set clrcount = 0 for those rows - this will work with your current query.

However, if they must be deleted then the answer by Joel Coehoorn is your way to go.

Upvotes: 1

Joel Coehoorn
Joel Coehoorn

Reputation: 415870

You want a separate table that defines your possible colors. Then you join from it, like this:

select c.color, sum(clrcount)
from colors c
left join tmp t on t.color = c.color
group by c.color

Upvotes: 0

Related Questions