Count data from multiple tables using SUM

Question

I have table with positions

tbl_positions
   id position
    1 Driver
    2 Lobby
    3 Support
    4 Constructor

and in other table i have users

EDIT:

   tbl_workers
      id   name   position position2 status
       1   John   2        3         4
       2   Mike   3        2         2
       3   Kate   2        0         3
       4   Andy   1        0         0

i do request of positions

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 tbl_workers AS w 
      ON w.position=p.id
GROUP BY p.id, p.position

I need output like this in single query.

Position    booked placed
Driver        0       0
Lobby         1       2
Support       0       2
Constructor   0       0

I need to evalate both field positon1 and position2 instead of one. I think its easy to modify it but i cannot find the right solution please help.

EDIT : Added status 4

Andriy M · Accepted Answer · 2011-09-26 09:00:22Z

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.

Thanks, but i forgot to mention status 4 on workers table if this status used then position is placed and position2 is booked can you modify your answer to correctly process this please?

Yahia · Accepted Answer · 2011-09-24 09:17:26Z

0

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

edited Sep 24, 2011 at 9:17

answered Sep 24, 2011 at 6:50

Yahia

70.5k9 gold badges117 silver badges147 bronze badges

Comments

DRapp · Accepted Answer · 2011-09-24 16:41:20Z

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

Collectives™ on Stack Overflow

Count data from multiple tables using SUM

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related