SQL Query using GROUP BY Statment

Question

I have this table in my database:

╔════╦═══════╦═══════╗
║ id ║ Name  ║ Price ║
╠════╬═══════╬═══════╣
║  1 ║ ciao  ║   123 ║
║  2 ║ ciao  ║    55 ║
║  3 ║ bye   ║    43 ║
║  4 ║ hello ║    12 ║
║  5 ║ ciao  ║     1 ║
║  6 ║ ciao  ║    77 ║
╚════╩═══════╩═══════╝

..and i wound like to create a new view displaying two columns:

1) "ciao" or "not ciao"

2) how many rows in the previous table are "ciao" or "not ciao", something like this:

╔════╦══════════╦═══════╗
║ id ║   Name   ║ Count ║
╠════╬══════════╬═══════╣
║  1 ║ ciao     ║     4 ║
║  2 ║ not ciao ║     2 ║
╚════╩══════════╩═══════╝

I'm trying to find a solution, but i'm not able to group by "not ciao" values:

SELECT Name, COUNT(*) 
FROM mytable
WHERE Name = "Ciao"
GROUP BY Name

Sergey Kalinichenko · Accepted Answer · 2015-03-04 15:25:12Z

3

You need to map everything except 'ciao' to 'not ciao', like this:

SELECT CASE WHEN Name!='ciao' THEN 'not ciao' ELSE Name END as Name, COUNT(*) 
FROM mytable
GROUP BY CASE WHEN Name!='ciao' THEN 'not ciao' ELSE Name END

answered Mar 4, 2015 at 15:25

Sergey Kalinichenko

729k85 gold badges1.2k silver badges1.6k bronze badges

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

Comments

Deep · Accepted Answer · 2015-03-04 15:25:24Z

2

This might work (not tested) :

SELECT CASE
         WHEN name = 'ciao' THEN 'ciao'
         ELSE 'not ciao'
       END as NAME,
       COUNT(*)
FROM   table
GROUP  BY CASE
            WHEN name = 'ciao' THEN 'ciao'
            ELSE 'not ciao'
          END

OR in MySQL

SELECT CASE
         WHEN name = 'ciao' THEN 'ciao'
         ELSE 'not ciao'
       END as NAME1,
       COUNT(*)
FROM   table
GROUP  BY NAME1

answered Mar 4, 2015 at 15:25

Deep

3,2021 gold badge15 silver badges21 bronze badges

Comments

jarlh · Accepted Answer · 2015-03-04 15:25:42Z

1

Easiest way:

SELECT Name, COUNT(*) 
FROM
(select case when name <> "Ciao" then "Not Ciao"
                  else "Ciao" end as name
 from mytable)
GROUP BY Name

answered Mar 4, 2015 at 15:25

jarlh

44.9k8 gold badges52 silver badges68 bronze badges

Collectives™ on Stack Overflow

SQL Query using GROUP BY Statment

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related