Count data from multiple tables using SUM?

Instead of joining to tbl_workers you could join to its unpivoted variation where position and position2 would be in the same column but in different rows Here's how the unpivoting might look like: SELECT w. Id, w. Name, CASE x.

Pos WHEN 1 THEN w. Position ELSE w. Position2 END AS position, w.

Status FROM tbl_workers AS w CROSS JOIN (SELECT 1 AS pos UNION ALL SELECT 2) AS x Here's the entire query, which is basically your original query with the above query substituting for the tbl_workers table: SELECT p. Id, p. Position, SUM(CASE w.

Status WHEN 2 THEN 1 ELSE 0 END) AS booked, SUM(CASE w. Status WHEN 3 THEN 1 ELSE 0 END) AS placed FROM tbl_positions AS p LEFT JOIN ( SELECT w. Id, w.Name, CASE x.

Pos WHEN 1 THEN w. Position ELSE w. Position2 END AS position, w.

Status FROM tbl_workers AS w CROSS JOIN (SELECT 1 AS pos UNION ALL SELECT 2) AS x ) AS w ON w. Position=p.Id GROUP BY p. Id, p.

Position UPDATE This is a modified script according to additional request in comments: SELECT p.Id, p. Position, SUM(CASE w. Status WHEN 2 THEN 1 ELSE 0 END) AS booked, SUM(CASE w.

Status WHEN 3 THEN 1 ELSE 0 END) AS placed FROM tbl_positions AS p LEFT JOIN ( SELECT w. Id, w. Name, CASE x.

Pos WHEN 1 THEN w. Position ELSE w. Position2 END AS position, CASE w.

Status WHEN 4 THEN CASE x. Pos WHEN 1 THEN 3 ELSE 2 END ELSE w. Status END AS status FROM tbl_workers AS w CROSS JOIN (SELECT 1 AS pos UNION ALL SELECT 2) AS x ) AS w ON w.

Position=p. Id GROUP BY p. Id, p.

Position The idea is to substitute the 4 status in the subselect with 3 or 2 depending on whether we are currently to pull position or position2 as the unified position The outer select keeps using the same logic as before.

Instead of joining to tbl_workers you could join to its unpivoted variation where position and position2 would be in the same column but in different rows. Here's how the unpivoting might look like: SELECT w. Id, w.Name, CASE x.

Pos WHEN 1 THEN w. Position ELSE w. Position2 END AS position, w.

Status FROM tbl_workers AS w CROSS JOIN (SELECT 1 AS pos UNION ALL SELECT 2) AS x Here's the entire query, which is basically your original query with the above query substituting for the tbl_workers table: SELECT p. Id, p. Position, SUM(CASE w.

Status WHEN 2 THEN 1 ELSE 0 END) AS booked, SUM(CASE w. Status WHEN 3 THEN 1 ELSE 0 END) AS placed FROM tbl_positions AS p LEFT JOIN ( SELECT w.Id, w. Name, CASE x.

Pos WHEN 1 THEN w. Position ELSE w. Position2 END AS position, w.

Status FROM tbl_workers AS w CROSS JOIN (SELECT 1 AS pos UNION ALL SELECT 2) AS x ) AS w ON w. Position=p. Id GROUP BY p.

Id, p. Position UPDATE This is a modified script according to additional request in comments: SELECT p. Id, p.

Position, SUM(CASE w. Status WHEN 2 THEN 1 ELSE 0 END) AS booked, SUM(CASE w. Status WHEN 3 THEN 1 ELSE 0 END) AS placed FROM tbl_positions AS p LEFT JOIN ( SELECT w.

Id, w.Name, CASE x. Pos WHEN 1 THEN w. Position ELSE w.

Position2 END AS position, CASE w. Status WHEN 4 THEN CASE x. Pos WHEN 1 THEN 3 ELSE 2 END ELSE w.

Status END AS status FROM tbl_workers AS w CROSS JOIN (SELECT 1 AS pos UNION ALL SELECT 2) AS x ) AS w ON w. Position=p. Id GROUP BY p.Id, p.

Position The idea is to substitute the 4 status in the subselect with 3 or 2 depending on whether we are currently to pull position or position2 as the unified position. The outer select keeps using the same logic as before.

– MrWhddite333 Sep 26 at 4:58 @MrWhddite333: Please have a look at my update. – Andriy M Sep 26 at 9:01 Thanks anydriy M I appreciate your work. – MrWhddite333 Sep 26 at 17:10.

Not really sure what you mean by "evaluate both fields" - but here is an example on how to sum up both: SELECT P. ID, P. POSITION, ((SELECT SUM (CASE W.

STATUS WHEN 2 THEN 1 ELSE 0 END) FROM TBL_WORKERS W WHERE W. POSITION = P. ID) + (SELECT SUM (CASE W.

STATUS WHEN 2 THEN 1 ELSE 0 END) FROM TBL_WORKERS W WHERE W. POSITION2 = P. ID)) AS BOOKED, ((SELECT SUM (CASE W.

STATUS WHEN 3 THEN 1 ELSE 0 END) FROM TBL_WORKERS W WHERE W. POSITION = P. ID) + (SELECT SUM (CASE W.

STATUS WHEN 3 THEN 1 ELSE 0 END) FROM TBL_WORKERS W WHERE W. POSITION2 = P. ID)) AS PLACED FROM TBL_POSITIONS P.

Since your data is not normalized (ie: two columns both representing a position in a single row), I would do a UNION of the workers first, then apply your outer count/group by... something like... Each part of the inner query is pre-aggregating and only counting for the records qualifying the status 2 or 3 and have a position. So, if you have a large table, it would be more noticeable for performance. Then, that result gets summed at the outer level for all possible positions using COALESCE() to prevent NULL values in your output result.

SELECT p. Id, p. Position, coalesce( SUM(w.

Booked), 0 ) AS booked, coalesce( SUM(w. Placed), 0 ) AS placed FROM tbl_positions AS p LEFT JOIN ( select w1. Position as PositionID, sum( if( w1.

Status = 2, 1, 0 )) as Booked, sum( if( w1. Status = 3, 1, 0 )) as Placed from tbl_workers w1 where w1. Status in ( 2, 3 ) and w1.

Position > 0 group by PositionID union all select w2. Position2 as PositionID, sum( if( w2. Status = 2, 1, 0 )) as Booked, sum( if( w2.

Status = 3, 1, 0 )) as Placed from tbl_workers w2 where w2. Status in ( 2, 3 ) and w2. Position2 > 0 group by PositionID ) as w on p.Id = w.

PositionID group by p. Id, p.position.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.