mysql: display data shiftwise

Question

I have this example table:

Table1

+-------+----------------+-----------+------------+
| tkno  |      dete      |   shyft   |   Name     |
+-------+----------------+-----------+------------+
| 1251  |     13-04-2018 |    Shift1 |         Foo|
| 1252  |     13-04-2018 |    Shift1 |         Foo|
| 1253  |     14-04-2018 |    Shift2 |         Bar|
| 1254  |     14-04-2018 |    Shift2 |         Bar|
| 1255  |     15-04-2018 |    Shift3 |         Bar|
| 1256  |     15-04-2018 |    Shift3 |         Joe|
| 1257  |     15-04-2018 |    Shift3 |         Joe|
+-------+----------------+-----------+------------+
3 rows in set (0.09 sec)

When I use this query,

SELECT shyft, 
(SELECT COUNT(tkno) FROM Table1 WHERE dete = DATE(now())) AS 'Todays Tickets',
(SELECT COUNT(tkno) FROM Table1 WHERE dete = DATE_SUB(DATE(now()), INTERVAL 1 DAY)) AS '1 Day Ago',
(SELECT COUNT(tkno) FROM Table1 WHERE dete = DATE_SUB(DATE(now()), INTERVAL 2 DAY)) AS '2 Days Ago'
FROM Table1
GROUP BY shyft;

I get results like this:

+-------+----------------+-----------+------------+
| shyft | Todays Tickets | 1 Day Ago | 2 Days Ago |
+-------+----------------+-----------+------------+
|Shift1 |             13 |        33 |         34 |
|Shift2 |             13 |        33 |         34 |
|Shift3 |             13 |        33 |         34 |
+-------+----------------+-----------+------------+
3 rows in set (0.09 sec)

I want to display the number of tickets in each shift for current day and last 2 days. For the above query, it is showing the same count on row 2 and 3. Please help me with where am I going wrong. Thanks!

Colin 't Hart · Accepted Answer · 2018-05-27 12:47:44Z

1

The subqueries in your query are al counting all rows of the table. They should probably only be counting for the "shyft" from the main table; something like:

SELECT shyft, 
(SELECT COUNT(tkno) FROM Table1 WHERE shyft=x.shyft AND dete = DATE(now())) AS 'Todays Tickets',
(SELECT COUNT(tkno) FROM Table1 WHERE shyft=x.shyft AND dete = DATE_SUB(DATE(now()), INTERVAL 1 DAY)) AS '1 Day Ago',
(SELECT COUNT(tkno) FROM Table1 WHERE shyft=x.shyft AND dete = DATE_SUB(DATE(now()), INTERVAL 2 DAY)) AS '2 Days Ago'
FROM Table1 x
GROUP BY shyft;

answered May 27, 2018 at 12:47

Colin 't Hart

9,52015 gold badges37 silver badges44 bronze badges

That really worked! :-) Thanks Colin, I was messing a lot to find this...

Irfan Junaid
– Irfan Junaid

2018-05-27 13:41:25 +00:00
Commented May 27, 2018 at 13:41
But the query itself is very slow and takes around 3 mins to run... I had to follow this stackoverflow.com/questions/10563619/… to run successfully. Anyway to make run faster?

Irfan Junaid
– Irfan Junaid

2018-05-27 13:45:02 +00:00
Commented May 27, 2018 at 13:45

Add a comment |

Stack Exchange Network

mysql: display data shiftwise

1 Answer 1

Your Answer

Hot Network Questions

mysql: display data shiftwise

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions