Select different values of a column having count more than one

Question

I have a table like this

SA_ID ---+---Acct_Id---+---SA_Type
101           111           TYPE1
102           111           TYPE2
103           112           TYPE1

I have a query to get acct_id having more than one sa_type

select acct_id,count(*) from sa_tbl
having count(*) > 1
group by acct_id;

I get a result like this

acct_id ---+---count(*)
111             2

But I need to get the result like this:

acct_id ---+---count(*)-----+sa_type1----+---sa_type2
    111             2        TYPE1            TYPE2

What purpose will a 'variable column' output serve? If you want more complex outputs like this, try creating a Procedure. — Ashis Kumar Sahoo, Feb 03 '14 at 10:32

score 2 · Accepted Answer · answered Feb 03 '14 at 11:12

I tried to get result like you mentioned in question but able to get something similar to this check it on SQL Fiddle

SELECT tbl.`Acct_Id` AS 'Acct_Id',COUNT(`Acct_Id`)  AS 'counts',
GROUP_CONCAT(`SA_Type`) AS 'types'
FROM `sa_tbl` AS tbl 
GROUP BY tbl.`Acct_Id` 
HAVING counts > 1

hope this will help you !

score 0 · Answer 2 · edited May 23 '17 at 12:28

0

Check the pivot function, see question here MySQL pivot table

This function works for Oracle and MySQL. For other databases (if you need) you can check additionally by yourself.

edited May 23 '17 at 12:28

Community

1
1

answered Feb 03 '14 at 11:16

smnbbrv

23,502
9
78
109

score 0 · Answer 3 · answered Feb 03 '14 at 11:30

If your RDBMS doesn't support PIVOT (as was already suggested to you), you can use a combination of MAX / CASE / GROUP BY:

select 
  acct_id,
  max((case when sa_type = 'TYPE1' then sa_id else NULL end)) as type1,
  max((case when sa_type = 'TYPE2' then sa_id else NULL end)) as type2
from sa_tbl
group by acct_id

Output:

acct_id type1   type2
112     103 
111     101     102

SQL Fiddle

Select different values of a column having count more than one

3 Answers3