SQL - In one query, count how many employees have the last name ‘Zykh’ and how many have the last na-CodePudding

I have a problem on this particular sql query.

I have list of last names in the column emp_last_name in the table emp_last_name (same table and column name)

And I wanted to search for the 2 last names using the emp_last_name table.

This is what I came up with:

select count(a.emp_last_name) Zykh, count(b.emp_last_name) Vickson
from emp_last_name a, emp_last_name b
WHERE a.emp_last_name LIKE 'Zykh%' and b.emp_last_name LIKE 'Vickson%';

but it seems to show an incorrect result with having the same value from both tables. btw the incorrect value is 28712 for both tables (dunno how it came to that)

I learned the right value by having to make two queries but it is not what was asked.

This is what I wanted it to look like:

Zykh	Vickson
148	194

can someone help?

CodePudding user response：

A typical way to approach this (the where statement is not necessary, but might improve performance if you have a large table):

SELECT
   SUM(CASE WHEN a.emp_last_name LIKE 'Zykh%' THEN 1 ELSE 0 END) AS Zykh, 
   SUM(CASE WHEN a.emp_last_name LIKE 'Vickson%' THEN 1 ELSE 0 END) AS Vickson
FROM emp_last_name a
WHERE a.emp_last_name LIKE 'Zykh%' 
   OR a.emp_last_name LIKE 'Vickson%';

To explain the result of 28,712 your received before: you created a cross join (Cartesian Product) between the a and b table, resulting in the product of both answers (148*194 = 28,712)