CODEWITHSUNDEEP
sqlsql-servert-sql
Exa
Exa

Reputation: 11

SQL join multiple sub-queried columns together

For example let's say I have an SQL table with 12 rows that looks like the table on the left below, and I want to create the summary table of aggregated row counts on the right.

Image

Here is an SQLFiddle of this example: http://sqlfiddle.com/#!9/ee508f/1

I can get each of those 4 summary columns individually with the queries below:

SELECT Product, COUNT(*) AS bad FROM mytable WHERE Quality IN (1,2,3) GROUP BY Product;
SELECT Product, COUNT(*) AS acceptable FROM mytable WHERE Quality IN (4,5,6) GROUP BY Product;
SELECT Product, COUNT(*) AS good FROM mytable WHERE Quality IN (7,8,9) GROUP BY Product;
SELECT Product, COUNT(*) AS great FROM mytable WHERE Quality IN (10) GROUP BY Product;

But how can I join these 4 results together into a single table like the one shown in my image? I want a solution that can flatly add on additional columns (rather than having another level of nesting), since in my actual project I have 12 columns like this and deeply nesting that many queries would not be very readable.

Upvotes: 0

Views: 53

Answers (1)

Error_2646
Error_2646

Reputation: 3781

Why do you need multiple queries though?

SELECT Product,
       SUM(CASE WHEN Quality IN (1,2,3) THEN 1
                ELSE 0
            END
          ) AS good,
       SUM(CASE WHEN Quality IN (4,5,6) THEN 1
                ELSE 0
            END
          ) AS fair,
       ...
       ...
  FROM YOUR_TABLE
 GROUP
    BY Product

Upvotes: 5

Related Questions