How to count string in column with select unique string by other column

Question

Trying to solve the problem find that, but it is not what i need...

select distinct column1, count(column2) from table group by column1;


    column1      column2
    aaa          type1
    bbb          type3
    ccc          type1
    aaa          type1
    aaa          type2
    aaa          type1
    ccc          type3
    aaa          type1
    bbb          type3
    aaa          type3
    aaa          type3

Expected result is:

    aaa type1 = 4, type2 = 1, type3 = 2
    bbb type3 = 2
    ccc type1 = 1, type3 = 2

then use column_2 for grouping

Suraj
– Suraj

2016-10-04 09:14:39 +00:00
Commented Oct 4, 2016 at 9:14 — Suraj
– Suraj, Commented Oct 4, 2016 at 9:14

klin · Accepted Answer · 2016-10-04 09:28:42Z

2

select 
    column1, 
    string_agg(format('%s = %s', column2, count), ', ') counts
from (
    select *, count(column2) 
    from test 
    group by 1, 2
    order by 1, 2
    ) s
group by 1
order by 1

 column1 |             counts              
---------+---------------------------------
 aaa     | type1 = 4, type2 = 1, type3 = 2
 bbb     | type3 = 2
 ccc     | type1 = 1, type3 = 1
(3 rows)

answered Oct 4, 2016 at 9:28

klin

123k15 gold badges240 silver badges262 bronze badges

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

Comments

Matt · Accepted Answer · 2016-10-04 09:33:40Z

0

From your expected output this is what I think your are after.

Use a COUNT function with a nested CASE statement.

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

Output:

Column1 Type1Count Type2Count Type3Count
aaa     4          1          2
bbb     0          0          2
ccc     1          0          1

Or to make it exactly as you wanted it (not recommended):

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

Output:

Column1 Type1Count  Type2Count  Type3Count
aaa     type1 = 4   type2 = 1   type3 = 2
bbb     type1 = 0   type2 = 0   type3 = 2
ccc     type1 = 1   type2 = 0   type3 = 1

edited Oct 4, 2016 at 9:33

answered Oct 4, 2016 at 9:22

Matt

15.1k28 gold badges109 silver badges161 bronze badges

6 Comments

Mikhail Over a year ago

thanks a lot, but data in table is dynamic, and what if i don't know values in column2 from the begining? i can't write something like WHEN 'type1'

Matt Over a year ago

You could use a SQL Pivot Function more than likely.

Matt Over a year ago

@a_horse_with_no_name Duly noted and amended :)

Mikhail Over a year ago

Please, and what about if there is one else column0, how make query show values that corresponding column1?

Mikhail Over a year ago

in previous context query

|

Collectives™ on Stack Overflow

How to count string in column with select unique string by other column

2 Answers 2

Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related