How to club all values of MySql?

Question

I am using UNION ALL to club the values.

SELECT COUNT(*) as votesfromIndia FROM voting WHERE MobNo LIKE '91%'
UNION ALL
SELECT COUNT(*) as votesfromUS FROM voting WHERE MobNo LIKE '1%'
UNION ALL
SELECT COUNT(*) as votesfromBelgium FROM voting WHERE MobNo LIKE '32%'
UNION ALL
SELECT COUNT(*) as votesfromHK FROM voting WHERE MobNo LIKE '852%'
UNION ALL
SELECT COUNT(*) as votesfromIsrael FROM voting WHERE MobNo LIKE '972%'

Result is :

But I want country wise:

Fields I am having: name, mobno. Any help will be appreciated.

GMB · Answer 1 · 2020-01-23T14:18:29.173

You can add another column to each unioned query with a constant value that shows the country each row corresponds to:

SELECT 'India' country, COUNT(*) as votes FROM tbl_javoting WHERE nMobNo LIKE '91%'
UNION ALL
SELECT 'US', COUNT(*) FROM tbl_javoting WHERE nMobNo LIKE '1%'
UNION ALL
SELECT 'Belgium', COUNT(*) FROM tbl_javoting WHERE nMobNo LIKE '32%'
UNION ALL
SELECT 'Hong Kong', COUNT(*) FROM tbl_javoting WHERE nMobNo LIKE '852%'
UNION ALL
SELECT 'Israel', COUNT(*) FROM tbl_javoting WHERE nMobNo LIKE '972%'

Side note: it is not necessary (nor relevant) to give different names to the columns returned by each unioned query: the first query defines the column names for the whole resultset.

score 0 · Answer 2 · answered Jan 23 '20 at 14:18

0

I guess name is the country name?

SELECT name as Country, COUNT(*) as votes FROM tbl_javoting group by name

answered Jan 23 '20 at 14:18

Jens

67,715
15
98
113

score 0 · Answer 3 · answered Jan 23 '20 at 16:37

Use a case expression for aggregation:

SELECT (CASE WHEN MobNo LIKE '91%' THEN 'India'
             WHEN MobNo LIKE '1%' THEN 'US'
             WHEN MobNo LIKE '32%' THEN 'Belgium' 
             WHEN MobNo LIKE '852%' THEN 'HK' 
             WHEN MobNo LIKE '972%' THEN 'Israel'
        END) as location,
        COUNT(*) as votes
FROM voting 
GROUP BY location;

If you want the results in separate columns:

SELECT SUM(MobNo LIKE '91%') as India, 
       SUM(MobNo LIKE '1%') as US, 
       SUM(MobNo LIKE '32%') as Belgium, 
       SUM(MobNo LIKE '852%') as HK, 
       SUM(MobNo LIKE '972%') as Israel
FROM voting ;

How to club all values of MySql?

3 Answers3