Mysql query two tables & get data in date wise or user wise

Question

I have two tables. The first one holds information about Hours:

ID   |  activitydate   |  userid  |  phaseid  |  activityid  |  time  |
 3   |  2011-04-11     |    1     |     1     |      1       |  120   |
 4   |  2011-04-12     |    1     |     1     |      1       |  180   |
 5   |  2011-04-12     |    3     |     1     |      1       |  360   |
 6   |  2011-04-22     |    2     |     2     |      3       |  300   |

The 2nd table holding the data of Users:

  ID   |    userid       |  lastname |
   1   |      abc        |   ABC     |
   2   |      xyz        |   XYZ     |
   3   |      asd        |   ASD     |
   4   |      qwe        |   QWE     |

I need a query which generate below given format:

  Date     | ABC | XYZ | ASD | QWE |
2011-04-11 | 120 |  0  |  0  |  0  |
2011-04-12 | 180 |  0  | 360 |  0  |
2011-04-22 |  0  | 300 |  0  |  0  |

This can be as below format:

  User  | 2011-04-11 | 2011-04-12 | 2011-04-13| .... | 2011-04-22 |...
  ABC   |    120     |    180     |     0     | .....|     0      |...
  XYZ   |     0      |     0      |     0     | .....|    300     |...
  ASD   |     0      |    360     |     0     | .....|     0      |...
  QWE   |     0      |     0      |     0     | .....|     0      |...

Any help is really appreciated!

This is called pivoting. Try searching for PIVOT: stackoverflow.com/search?q=mysql+PIVOT — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Jul 13, 2011 at 9:18
It's better to do it in your application (site) as there is one main issue: What happens if you have too many columns (e.g. 100 users or dates) to show? — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Jul 13, 2011 at 9:19
Thanks ypercube, for your quick replay. In my DB, there are only 40 users and i can filter date, for ex: with in the month of April.. — Chandru
– Chandru, Commented Jul 13, 2011 at 9:47
That would still require a 31+ columns (per date) statement (or 40+ per user), with repeating CASE. — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Jul 13, 2011 at 9:50
And if next month you have 2 new users, you'll have to change the SQL. That is not good. — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Jul 13, 2011 at 9:50

ypercubeᵀᴹ · Accepted Answer · 2011-07-13 10:00:10Z

0

This would do:

SELECT 
    u.lastname AS `User`
  , SUM(CASE WHEN activitydate='2011-04-01' THEN time ELSE 0 END) AS `2011-04-01`
  , SUM(CASE WHEN activitydate='2011-04-02' THEN time ELSE 0 END) AS `2011-04-02`
  , ...
  , SUM(CASE WHEN activitydate='2011-04-30' THEN time ELSE 0 END) AS `2011-04-30`
FROM Hours h
  JOIN Users u
    ON h.userid = u.userid
WHERE h.activitydate BETWEEN '2011-04-01' AND '2011-04-30'
GROUP BY h.userid
ORDER BY h.userid

answered Jul 13, 2011 at 10:00

ypercubeᵀᴹ

116k19 gold badges181 silver badges249 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Mysql query two tables & get data in date wise or user wise

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related