Aggregate query in same select statement

Question

I have a select statement which is returning me 5 columns (and it is taking 1-2 minutes) one column in this is color (i.e. only three colors Red, Blue and Yellow). Now I want to show the count of the number of rows having Red, Number of rows having Blue and No. of rows having yellow in the same select statement.

Select statement is this

enter image description here

and I want output like this

enter image description here

Any help ?

1-2 minutes? How many tables and rows is it operating on? If you add the SQL statement to your post, that will help us help you. — Eric H, Feb 08 '12 at 18:36

Lamak · Accepted Answer · 2012-02-08T18:51:29.070

1

You want this (For SQL Server 2005+):

SELECT  *, SUM(CASE WHEN Color = 'RED' THEN 1 ELSE 0 END) OVER() [RED],
        SUM(CASE WHEN Color = 'BLUE' THEN 1 ELSE 0 END) OVER() [BLUE],
        SUM(CASE WHEN Color = 'Yellow' THEN 1 ELSE 0 END) OVER() [Yellow]
FROM YourTable

edited Feb 08 '12 at 18:51

answered Feb 08 '12 at 18:38

Lamak

69,480
12
108
116

1

You either change `SUM()` to `COUNT()` or chnage the case to: `... THEN 1 ELSE 0 END` – ypercubeᵀᴹ Feb 08 '12 at 18:46
This has TERRIBLE performance. This will execute 4 times as slowly. – usr Feb 08 '12 at 18:47
1

@ypercube I added the `ELSE`. @usr I was more focused in the **single select** part of the question – Lamak Feb 08 '12 at 18:51
@lamak Thanks for your answer. :-) I really appreciate it. you just saved my day ! – Zerotoinfinity Feb 08 '12 at 18:52
@ypercube +1 for the suggestion. I have modified my query as per your suggection. – Zerotoinfinity Feb 08 '12 at 18:53
I have took my select query in CTE based on condition and than I have implemented your query, I didn't notice any performance loss, yet – Zerotoinfinity Feb 08 '12 at 18:56

ypercubeᵀᴹ · Answer 2 · 2012-02-08T19:17:41.437

1

Using CTEs (Common Table Expressions):

; 
WITH cte AS
( SELECT ...
  ... your query here
) 
WITH grp AS
( SELECT 
        color
      , COUNT(*) AS cnt
  FROM
        cte
  GROUP BY
        color
) 
SELECT 
      cte.*
    , (SELECT cnt FROM grp WHERE color = 'Red')    AS Red
    , (SELECT cnt FROM grp WHERE color = 'Blue')   AS Blue
    , (SELECT cnt FROM grp WHERE color = 'Yellow') AS Yellow
FROM 
      cte

edited Feb 08 '12 at 19:17

answered Feb 08 '12 at 18:55

ypercubeᵀᴹ

113,259
19
174
235

Yeah ! same thing I have done and made a comment to Lamak post. But thanks. +1 to mention that.. I am sure others will also learn from this query – Zerotoinfinity Feb 08 '12 at 18:57
I've missed 2 lines previously. Corrected now :) – ypercubeᵀᴹ Feb 08 '12 at 19:18

Aggregate query in same select statement

2 Answers2