Home Changing 3 column to 1 and taking a count of its customers in SQL Server
Reply: 3

Changing 3 column to 1 and taking a count of its customers in SQL Server

user2486873
1#
user2486873 Published in 2018-02-13 19:46:17Z

I have this following that I need to roll to "Chinese" only and take a count of all these 3 Chinese languages:

so my desired output is one row as follows:

Chinese as PrimaryLanguage and 900 as Total count. The rest of the list contains a lot of other languages that I don't need to roll.

Thanks in advance.

Md. Suman Kabir
2#
Md. Suman Kabir Reply to 2018-02-13 19:55:30Z

You can try this simple query :

select 'Chinese' as PrimaryLanguage, #Count from (
    select count(*) as #Count from table1
    where language in ('Chinese', 'Chinese-Cantonese', 'Chinese-Mandarin')
) as T
Gordon Linoff
3#
Gordon Linoff Reply to 2018-02-13 19:48:54Z

You can use case:

select (case when language in ('Chinese', 'Chinese-Cantonese', 'Chinese-Mandarin')
             then 'Chinese' else language
        end) as language,
       count(*)
from t
group by  (case when language in ('Chinese', 'Chinese-Cantonese', 'Chinese-Mandarin')
                then 'Chinese' else language
           end)

If you want all Chinese languages, you can change the conditions to language like 'Chinese%'.

Michał Turczyn
4#
Michał Turczyn Reply to 2018-02-13 20:10:44Z

Try this:

select 'Chinese', sum([#Count]) from MY_TABLE where primaryLanguage like '%Chinese%'
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.296909 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO