CODEWITHSUNDEEP
sql
Ivan Cheung
Ivan Cheung

Reputation: 21

How to count data with specify word?

I am a new face to SQL. Suppose I have a set of data:

==================
|| iphone6      ||
|| iphone7      ||
|| iphone7s     ||
|| Android 7.1  ||
|| Android 6.3  ||
==================

And I want to get the result like this:

||Model      ||Amount   ||
==========================
||iphone     ||        3||
||Android    ||        2||
==========================

And my code is like this:

SELECT
CASE
   WHEN model LIKE '%Android%' THEN 'Android'
   WHEN model LIKE '%iPhone%' THEN 'IPhone'
END,
, COUNT(*) AS Amount FROM #table GROUP BY model;

But my output is :

||Model     ||    Amount||
==========================
||IPhone    ||         1||
||IPhone    ||         1||
||IPhone    ||         1||
||Android   ||         1||
||Android   ||         1||
==========================

How can I fix it?

Upvotes: 1

Views: 48

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

You just need to aggregate by the column you are creating:

SELECT (CASE WHEN model LIKE '%Android%' THEN 'Android'
             WHEN model LIKE '%iPhone%' THEN 'IPhone'
        END),
       COUNT(*) AS Amount
FROM #table
GROUP BY (CASE WHEN model LIKE '%Android%' THEN 'Android'
               WHEN model LIKE '%iPhone%' THEN 'IPhone'
          END);

Upvotes: 4

Related Questions