MySQL Add Row on SELECT Query

Question

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

LoztInSpace · Accepted Answer · 2018-08-28 10:25:40Z

1

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>

answered Aug 28, 2018 at 10:25

LoztInSpace

5,6971 gold badge17 silver badges29 bronze badges

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

Comments

Marco · Accepted Answer · 2018-08-28 10:31:20Z

1

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

edited Aug 28, 2018 at 10:31

Marco

57.7k15 gold badges135 silver badges159 bronze badges

answered Aug 28, 2018 at 10:27

Oto Shavadze

43.3k56 gold badges169 silver badges248 bronze badges

Collectives™ on Stack Overflow

MySQL Add Row on SELECT Query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related