count grouping two tables to multiply the result

user_15839705 0
Viewed 49

I have this tables(simplified):

table1

date - type
22/05/23 - 1
22/05/23 - 1
22/05/23 - 2
22/05/24 - 1
22/05/25 - 2

table2

date - worker
22/05/23 - 20
22/05/24 - 23
22/05/25 - 17

I'm using this SELECT to count how many results are equal on each date

SELECT date, type, COUNT(*) FROM table1 GROUP BY date, type;

the result would be:

date - type - count
22/05/23 - 1 - 2
22/05/23 - 2 - 1
22/05/24 - 1 - 1
22/05/25 - 2 - 1

to find out which worker was on the date (using php) I'm doing it this way:

SELECT worker FROM table2 WHERE date = $date

and the result:

date - type - count - worker
22/05/23 - 1 - 2 - 20
22/05/23 - 2 - 1 - 20
22/05/24 - 1 - 1 - 23
22/05/25 - 2 - 1 - 17

now the problem: I need, in the same SELECT, to display this result, but grouping by worker (table2) and type (table1), because each type will have an amount to pay and now I need to manually add the values...

something like

SELECT *, SUM (SELECT type, COUNT(*) FROM table1 GROUP BY date AND SELECT worker FROM table2 WHERE date = $date) AS total

how could i do this?

mysql php
1 Answers

solved!

I don't know if it's the correct way, but it worked for my need.

I'll post it in case anyone needs it:

SELECT p.date, p.type, COUNT(p.typo) AS qnt FROM table1 AS p LEFT JOIN table2 AS c ON p.date = c.date GROUP BY p.date, c.worker, p.type

andthe result is as expected! it will count how many types each worker made on each date.

hope it helps someone too!

user_158397050
Related