Count total records by category and subcategory

Question

I have a table where all the employee data with country and city

Main table Tbl_Emp

ID  Name                       Dept       CountryID        CityID
1   Arun Bikas Das              IT          1               1
2   Abhineet Prakash            IT          1               1
3   Avnish Dhoundiyal           HR          2               5
4   Amit Batra                  HR          3               6
5   Ajay Sethi                  AC          3               6
6   Amarendra Pandey            AC          3               6
7   Abhijit Singh               ADM         1               3
8   Anil Kumar                  ADM         1               4
9   Mahesh Venu                 MAR         1               7
10  Mohamed Rafi                MAR         1               9

Tbl_County is

C_ID    Country
1       India
2       USA
3       UK

tbl_city is

C_ID    City      CountryID
1       Delhi       1
2       Delhi       1
3       Mumbai      1
4       Nagpur      1
5       NewYork     2
6       Caneda      3
7       Jaipur      1
9       Panjab      1

I want to select employees count by county:

 Country    Employees
 -------    ---------
 India          6 
 USA            1 
 UK             3

, and same for the city:

City      Employees
-------   ---------
Delhi           2 
Mumbai          1 
NewYork         1 
Canada          3 
Nagpur          1 
Jaipur          1 
Panjab          1

possible duplicate of [How to use count and group by at the same select statement](http://stackoverflow.com/questions/2722408/how-to-use-count-and-group-by-at-the-same-select-statement) — Bulat, Aug 06 '15 at 11:59
Join the both tables and then select count(id),country and use a group by — Arunprasanth K V, Aug 06 '15 at 12:01
try this select table1.Country,count(*) as TotalCount from Tbl_County table1 inner join Tbl_Emp table2 on table1.C_id=table2.countryid group by table1.country SELECT table2.City ,Count(*) AS TotalCount FROM Tbl_Emp AS table1 INNER JOIN Tbl_City AS table2 ON table1.City_ID = table2.C_ID GROUP BY table2.Country — Arunprasanth K V, Aug 06 '15 at 12:17

score 1 · Answer 1 · answered Aug 06 '15 at 12:03

Do the same for city. Please do a little more searching before posting, this is a very common thing to do and one that you should have found numerous examples on. Doing so will help reduce the number of low quality questions being posted.

SELECT C.Country, COUNT(*) AS CountryCount
FROM Tbl_Emp E
INNER JOIN TblCounty C
    ON E.CountryID = C.C_ID
GROUP BY C.Country

Praveen · Accepted Answer · 2015-08-06T13:01:05.737

For Country Count:

select 
min(c.Country),
count(e.CountryID) as Employees
from 
Tbl_Emp e
join Tbl_County c on e.CountryID = c.c_id
group by e.CountryID

For city count:

select 
min(c.city),
count(e.CityID) as Employees
from 
Tbl_Emp e
join tbl_city c on e.CityID = c.c_id
group by e.CityID

This can be an other solution:

select 
    c.Country,
    e.Employees
from (
    select
        CountryID, count(CountryID) as Employees
    from Tbl_Emp
    group by CountryID
) as e  
join Tbl_County c on e.CountryID = c.c_id


select 
    c.city,
    e.Employees
from (
    select
        CityID, count(CityID) as Employees
    from Tbl_Emp
    group by CityID
) as e
join tbl_city c on e.CityID = c.c_id

score 0 · Answer 3 · answered Aug 06 '15 at 12:07

SQL COUNT(*) :

The COUNT(*) function returns the number of records in a table:

SELECT COUNT(*) FROM table_name;

Following query will give you count based on country :

SELECT        COUNT(*) AS Count, Tbl_Country.Country
FROM            Tbl_Country INNER JOIN
                         Tbl_Emp ON Tbl_Country.C_ID = Tbl_Emp.CountryId
GROUP BY Tbl_Country.Country

Give a try for City query ,syntax same as above

Count total records by category and subcategory

3 Answers3