CODEWITHSUNDEEP
postgresql
Mikhail
Mikhail

Reputation: 31

Add column to SQL query

There is some select

SELECT column1, 
COUNT(CASE column2 WHEN 'type1' THEN 1 ELSE NULL END) AS Type1Count,
COUNT(CASE column2 WHEN 'type2' THEN 1 ELSE NULL END) AS Type2Count,
COUNT(CASE column2 WHEN 'type3' THEN 1 ELSE NULL END) AS Type3Count
FROM Yourtable 
GROUP BY column1

help me please, how i can add column0 which values corresponds to column1 in select query, some thing like that

SELECT column0, column1, 
COUNT(CASE column2 WHEN 'type1' THEN 1 ELSE NULL END) AS Type1Count,
COUNT(CASE column2 WHEN 'type2' THEN 1 ELSE NULL END) AS Type2Count,
COUNT(CASE column2 WHEN 'type3' THEN 1 ELSE NULL END) AS Type3Count
FROM Yourtable 
GROUP BY column1

but my variant is not work...

Upvotes: 0

Views: 39

Answers (1)

IMSoP
IMSoP

Reputation: 97996

When you are selecting the exact value of some columns, and aggregating (e.g. COUNT(), SUM()) on others, you need to tell the database which is which in the GROUP BY clause.

If you say:

GROUP BY column0, column1

Then for every unique combination of column0 and column1, you will get an extra row of results, with the COUNT() expressions calculated across all the rows in the table for that combination.

If you only want one row for each distinct value of column1, you need to instead tell the database which value of column0 you are interested in. For instance, you might ask for the minimum value of column0 for each column1 with this:

SELECT MIN(column0), column1 ... GROUP BY column1

Which means:

  • I want one row for each distinct value of ... (GROUP BY ...)
    • ... column1 (... column1)
  • For each row, show me (SELECT):
    • The minimum value of column0 in that group
    • The value of column1 which will be the same for everything in that group

Upvotes: 1

Related Questions