Using JOIN and COUNT to find totals SQL

Question

I am super new to SQL and have been working on this query and can not seem to get it to work. I need to sum the categories (how many clients are in each country and how many clients each employee has). This is what I tried to do to get the total clients in each country:

COUNT(*) TotalCount,
country.id, country.country
FROM client
INNER JOIN country
ON client.country_id = country.id
GROUP BY country.id, country.country

These are my tables:

CREATE TABLE employee(
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee VARCHAR(40)
);

CREATE TABLE country (
    id INT AUTO_INCREMENT PRIMARY KEY,
    country VARCHAR(40)
);

CREATE TABLE client(
    id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    name VARCHAR(40),
    email VARCHAR(40),
    sold BOOLEAN,
    date VARCHAR(40),
    email_type_id INT,
    employee_id INT,
    country_id INT,

    FOREIGN KEY(email_type_id) REFERENCES email_type(id),
    FOREIGN KEY(employee_id) REFERENCES employee(id),
    FOREIGN KEY(country_id) REFERENCES country(id)
);

Thank you so much!

Please provide some sample data and expected results. Cheers! — VBoka, Aug 27 '20 at 06:58
Why not provide some sample data (as INSERT statements), and a desired result? — Strawberry, Aug 27 '20 at 06:58

score 1 · Accepted Answer · answered Aug 27 '20 at 07:02

how many clients are in each country

select country , count(*) from country  inner join client on country.id=client.country_id 
group by country

how many clients each employee has

Select employee , count(*) from employee inner join client on client.employee_id =employee.id group by employee

score 0 · Answer 2 · edited Aug 27 '20 at 08:38

Country's Count:

SELECT country , count(*) as countryCount from country INNER JOIN client on country.id=client.country_id GROUP BY country;

Employee's Count:

SELECT employee , count(*) as empCount from employee INNER JOIN client on client.employee_id =employee.id GROUP BY employee;

You can also hit this query:::

SELECT country , count(country) as countryCount from country INNER JOIN client on client.country_id=country.id GROUP BY country;

SELECT employee , count(employee) as empCount from employee INNER JOIN client on employee.i=client.employee_id GROUP BY employee;

In count(), I have passed column name.

Using JOIN and COUNT to find totals SQL

2 Answers2