SQL Query - Get the count from many tables

Question

I have an employee table, dependent table

employee table

empid
empname
status(working,retired,deceased,terminated)

Dependent

id
empid
name
relationtoemp
age....etc

Now, i want to query to get the count of total employees, count of total employees in each status(working,retired..), count of all dependants for all employees

Any help?

take a look into `count()` - https://dev.mysql.com/doc/refman/5.7/en/counting-rows.html — treyBake, May 12 '17 at 10:57
you need to different query for this check count() and group by when you try and not get than come back here — krishn Patel, May 12 '17 at 10:59
Edit your question and provide sample data and desired results. — Gordon Linoff, May 12 '17 at 10:59
Possible duplicate of [Select count(\*) from multiple tables](http://stackoverflow.com/questions/606234/select-count-from-multiple-tables) — HReynaud, May 12 '17 at 11:20

score 0 · Answer 1 · answered May 12 '17 at 10:59

0

The keyword you're looking for is "Count"

For exemple : 'SELECT COUNT(empid) FROM employee WHERE status = working'

This will give you the number of employees which status is "working"

answered May 12 '17 at 10:59

HReynaud

54
1
9

YEs i got that with single table .but i need to include dependants table also and get the count in single query – ASD May 12 '17 at 11:02

jimmy8ball · Answer 2 · 2017-05-12T11:06:13.567

0

This will give you counts grouped by status

SELECT COUNT(emp.empid)  as counts, status
FROM employee emp join employee2 emp2 on emp.empid = emp2.empid
group by status

edited May 12 '17 at 11:06

answered May 12 '17 at 11:02

jimmy8ball

746
5
15

YEs i got that with single table .but i need to include dependants table also and get the count in single query – ASD May 12 '17 at 11:06
this will be only counting empid where there is a match to your dependent table, so this will do what you need – jimmy8ball May 12 '17 at 11:07
if there are no dependent records in table2 they will be removed form the count by the inner join – jimmy8ball May 12 '17 at 11:08

Filipe Martins · Answer 3 · 2017-05-12T11:30:50.920

You can try Prepared Statements like this:

set @sql = (
    select group_concat(distinct 
        concat(
            "sum(case when `status`='", e.status, "' then 1 end) as `", e.status, "`"
        )
    ) 
    from employee e
);

set @sql = concat("select count(e.empid) total_emp, (select count(*) from dependent) total_dep , ", @sql, " from employee e");

prepare stmt from @sql;

execute stmt;

The first part will dynamically generate columns for each employee status, and with a subselect you can count the total of dependents.

SQL Query - Get the count from many tables

3 Answers3