0

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!

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;
  • That really worked! :-) Thanks Colin, I was messing a lot to find this... – Irfan Junaid May 27 '18 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 May 27 '18 at 13:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.