CODEWITHSUNDEEP
mysql
Eric Sison
Eric Sison

Reputation: 3

MySQL Add Row on SELECT Query

My database structure: (table name is company_info)

 |comp_id | comp_name          | comp_nature
 | 001    | Citigroup, Inc.    | Bank
 | 002    | Harvard University | Academe
 | 003    | Western Union      | Courier
 | 004    | C.I.A.             | Government Agency

I wanted to create a query that would count comp_nature. But if comp_nature IS NOT "Bank" or "Academe", I wanted it to be categorized as "Other", so the result of the query should look something like this..

 | comp_nature       | count |
 | Bank              | 1     |
 | Academe           | 1     |
 | Other             | 2     |

I was able to use the COUNT keyword, but I couldn't seem to get how to create a "row" (Other) in a SELECT statement. (Or is this even possible in just one SELECT statement?).

Upvotes: 0

Views: 68

Answers (2)

Oto Shavadze
Oto Shavadze

Reputation: 42753

Select 
    case 
        when comp_nature not in ('Academe', 'Bank') then 'other' 
        else comp_nature end as col, 
    count(*) from t 
group by col

Upvotes: 1

LoztInSpace
LoztInSpace

Reputation: 5697

It will be something like this:

select comp_nature, count(*) as cnt from
(
select 
 case when comp_nature in('bank','acadme') then comp_nature 
 else 'Other' end as comp_nature 
from company_info
) as X
group by comp_nature
order by <whatever makes sense>

Upvotes: 1

Related Questions