To make long story short:
I have table like this
nr sat col
1 1 dsag
1 2 gds
1 2 gds
2 1 gdsa
2 2 gf
2 3 gdsa
And expect the result like this:
nr Sat_1 Sat_2 Sat_3
1 1 2 0
2 1 1 1
I want new column for each row from "SELECT DISTINCT sat" and next group by nr
This can be done using conditional aggregation:
select nr,
count(*) filter (where sat = 1) as sat_1,
count(*) filter (where sat = 2) as sat_2,
count(*) filter (where sat = 3) as sat_3
from the_table
group by nr
order by nr;
Online example: http://rextester.com/YVIR75756
filter is part of the optional "Advanced OLAP operations". Does it qualify as standard?I like a_horse_with_no_name's answer. This is shorter, although it might be slightly slower:
select nr,
sum((sat = 1)::int) as sat_1,
sum((sat = 2)::int) as sat_2,
sum((sat = 3)::int) as sat_3
from the_table
group by nr
order by nr;
The more typical way to write the query is using case:
select nr,
sum(case when sat = 1 then 1 else 0 end) as sat_1,
. . .
This happens to be standard SQL that will run in many databases.
COUNT(DISTINCT col)withGROUP BY nr, satto get the numbers, and aPIVOTto get your desired display. To make this short story longer, please show what you've tried already.