UNION, SUM and count(*) together in PHP SQL query

Question

So I have php SQL query that queries the floors and the total workstations on each floor.

config_location_workstation  workstation          asset_inventory

floor                        floor                floor
workstation_number           workstation_number   workstation_number
workstation_name             assigned_user
(production|non-production)  machine_type

I have config_location_workstation table that consist of floor, workstation_number, workstation_name (Production, Non-Production).

Then, I have asset_workstation table that consist of floor, workstation_number, assigned_user, machine_type (Laptop, Desktop). workstation_number has all the users workstation number per floor.

I also have asset_inventory table that also has floor and workstation_number of each machine.

I was able to query the total count of workstations per floor. But I also need to pull up all the workstation_name that has 'Production' in it. I need to combine all the tables to produce one query to get the total count of each workstation, machines deployed to each floor, total head count per floor. I need to get the count of all users workstation number per workstation_name (Production, Non-Production).

This is the current code I have to pull up the floors and the total workstations.

$query=mysqli_query($conn,"SELECT workstation.floor, COUNT(*) TOTAL FROM ( SELECT floor FROM config_location_workstation UNION ALL SELECT floor FROM asset_workstation ) AS workstation GROUP BY floor ORDER BY floor");

I am expecting to output

+-------+-------------+------+------+------+------+-----+
| Floor | Head Count  | Workstations| Machines Deployed |
+-------+-------------+------+------+------+------+-----+
| 6TH   | 100         | 150         | 140               |
| 18TH  | 200         | 250         | 200               |
| 19TH  | 300         | 320         | 310               |
+-------+-------------+------+------+------+------+-----+

How am I going to do this in one query.

Answer 1

Your tables are not normalized: there appears to be duplicate information in all 3 tables. (unless workstation_number is not unique and floor/workstation_number is a composite key. I would advise against that...)

I would suggest a schema similar to this (assuming a many-to-many relationship between users and workstations):

Workstation              User_Workstation     User
id                 <- -  workstation_id     
workstation_number       user_id         - -> id
machine_type                                  name
name
floor

Each of the fields in Workstation describe an attribute of a workstation. Any information you may have gathered from the other two tables is now in one place. Don't Repeat Yourself! Duplicate data is evil.

The goal is

I need to combine all the tables to produce one query to get the

• total count of each workstation,
• machines deployed to each floor,
• total head count per floor.
• I need to get the count of all users workstation number per workstation_name (Production, Non-Production).

Individually, these are a piece of cake. (And, if you're running a script to get your results, it's all you need)

select count(id) number_of_workstations
from Workstation;

select count(id) workstations_per_floor, floor
from Workstation
group by floor

select count(u.id) users_per_floor, floor
from Workstation w
inner join User_Workstation uw on w.id = uw.workstation_id
inner join User u on u.id = uw.user_id
-- optionally filter by production or non-production
-- where w.name = ?
group by w.floor

But your desired result seems to be something different:

+-------+-------------+------+------+------+------+-----+
| Floor | Head Count  | Workstations| Machines Deployed |
+-------+-------------+------+------+------+------+-----+
| 6TH   | 100         | 150         | 140               |
| 18TH  | 200         | 250         | 200               |
| 19TH  | 300         | 320         | 310               |
+-------+-------------+------+------+------+------+-----+

I'm guessing that Head Count is number of users per floor, Workstations is total workstations per floor, and Machines Deployed is... I have no idea. Production?

select floor, count(u.id) Head_Count, count(distinct w.id) Workstations, -- ??? as Machines_Deployed
from workstation w
left join user_workstation uw on w.id = uw.workstation_id
left join user on u.id = uw.user_id
group by w.floor

(I'll modify this based on feedback on what Machines Deployed is)