How to create a MySQL view

Question

I have MySQL visits table as

visit_date        client_id
2019-01-01 08-00-00 1
2019-01-01 11-00-00 2

work_hours table as

hour    name
8        '08-00'
9        '09-00'
...
18        '18-00'

and tables clients as

  client_id client_name
  1           aaa
  2           bbb 
  3           ccc

How to create a view to get data?

client_id   hour_name    count
1        '08-00'    1
1        '09-00'    0   
.....
1        '18-00'    0
2        '08-00'    0
2        '09-00'    0
2        '10-00'    0
2        '11-00'    1
2        '12-00'    0
.....
2        '18-00'    0
3        '08-00'    0
3        '09-00'    0
.....    all zero
3        '18-00'    0

If a client has no visit, do you want him in your result set? — Joe Taras, Mar 25 '19 at 10:49

Joe Taras · Accepted Answer · 2019-03-25T12:03:35.280

3

Try this:

SELECT c.client_id, w.hour_name,
    (SELECT COUNT(1) 
    FROM visits v
    WHERE v.client_id = c.client_id
    AND HOUR(v.visit_date) = w.hour) as tot_visit
FROM clients c
CROSS JOIN work_hours w
ORDER BY c.client_id, w.hour_name

See SQL Fiddle

edited Mar 25 '19 at 12:03

answered Mar 25 '19 at 11:57

Joe Taras

15,166
7
42
55

instead of "ORDER BY c.client_id, w.hour_name" may be "Group BY c.client_id, w.hour_name" . In that case query returns same result as in my answer. Thanks for your answer! – harp1814 Mar 25 '19 at 12:17
@harp1814: GROUP BY in your query is unuseful because you have no aggregate function and the cardinality is one for each couple (client id, hour name). With ORDER BY you have the right order (you have requested on your question) – Joe Taras Mar 25 '19 at 12:19

harp1814 · Answer 2 · 2019-03-25T12:35:28.950

0

SELECT c.client_id,
       wh.hour_name,

  (SELECT count(1)
   FROM visits
   WHERE hour(visit_date) = wh.hour
     AND client_id=c.client_id) AS chcount
FROM work_hours wh,
     clients c;

edited Mar 25 '19 at 12:35

answered Mar 25 '19 at 11:41

harp1814

1,494
3
13
31

How to create a MySQL view

2 Answers2