Add column to SQL query

Question

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...

i need unique result from column1, if i did SELECT column0, column1, … GROUP BY column0, column1 result of this query is all data, but i need unique column1 like in firest example. — Mikhail
– Mikhail, Commented Oct 4, 2016 at 15:16

IMSoP · Accepted Answer · 2016-10-04 15:33:46Z

1

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

edited Oct 4, 2016 at 15:33

answered Oct 4, 2016 at 15:17

IMSoP

99.8k18 gold badges135 silver badges183 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Mikhail Over a year ago

it's nearly result, but now i see in result column0 as i need, and don't see corresponding column1...

Mikhail Over a year ago

i try SELECT MIN(column0), 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

Mikhail Over a year ago

SELECT MIN(column0), MIN(column1) - worked for me!!!! If it's correct query this is what i need! :)

IMSoP Over a year ago

Your SELECT list doesn't contain column1. The results contain whatever columns you ask for in the SELECT list. I've edited slightly.

IMSoP Over a year ago

You don't need a MIN() around the thing you're grouping by - there's no "minimum" to take, because you have a different row for every distinct value.

Collectives™ on Stack Overflow

Add column to SQL query

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related