CODEWITHSUNDEEP
sqloracle-database
user3553846
user3553846

Reputation: 342

SELECT clause with same data using DISTINCT

Example in my employee table i having these data

//EMPLOYEE
E#       NAME        CITY
--------------------------
1        JOHN       ENGLAND
2        SITI       ENGLAND
3        JESS       GERMANY
4        HOLY       BOSTON

When i run this statement:

SELECT DISTINCT CITY, COUNT(E#) as "Total Employee" FROM EMPLOYEE Where CITY=;

what should i insert the value for 

//CITY=?

in order to get the result like this

CITY          TOTAL EMPLOYEE
----------------------------
ENGLAND              2
GERMANY              1
BOSTON               1

isn't impossible to use group by or having clause?

ANSWER FIXED ! THANKS

Upvotes: 0

Views: 78

Answers (1)

D Stanley
D Stanley

Reputation: 152556

Well, you're not excluding any cities, so you don't need a WHERE clause at all! But you DO need a GROUP BY to do a count. You can just do:

SELECT CITY, COUNT(E#) as "Total Employee" 
FROM EMPLOYEE 
GROUP BY CITY

However if you only want to include those three cities (even if more are added to the underlying data), you can do:

SELECT CITY, COUNT(E#) as "Total Employee" 
FROM EMPLOYEE 
WHERE CITY IN ('ENGLAND', 'GERMANY', 'BOSTON')
GROUP BY CITY

Upvotes: 4

Related Questions