I want to count city column's values which are repeated-CodePudding

id city
1  London
2  Rome
3  London
4  Rome

Expected output like this:

London    Rome
     2       2

Using case expression...

How can I solve this query?

CodePudding user response：

This is a standard aggregation group by having problem.

Sample data:

SQL> with test (id, city) as
  2    (select 1, 'London' from dual union all
  3     select 2, 'Rome'   from dual union all
  4     select 3, 'London' from dual union all
  5     select 4, 'Rome'   from dual union all
  6     select 5, 'Zagreb' from dual
  7    )

Query:

  8  select city,
  9         count(*) cnt
 10  from test
 11  group by city
 12  having count(*) > 1;

CITY          CNT
------ ----------
London          2
Rome            2

SQL>

What does case expression have to do with it?

CodePudding user response：

You can get your output using conditional aggregation:

SELECT COUNT(CASE city WHEN 'London' THEN 1 END) AS London,
       COUNT(CASE city WHEN 'Rome'   THEN 1 END) AS Rome
FROM   table_name
WHERE  city IN ('London', 'Rome')

Which, for the sample data:

CREATE TABLE table_name (id, city) AS
SELECT 1, 'London' FROM DUAL UNION ALL
SELECT 2, 'Rome' FROM DUAL UNION ALL
SELECT 3, 'London' FROM DUAL UNION ALL
SELECT 4, 'Rome' FROM DUAL;

Outputs:

LONDON	ROME
2	2

However, in this case, it is unclear how to handle your requirement for repeated values if the values were not repeated.

fiddle