I try to make a system of attendance of students to events. And I have three tables, one where you find official events, other where you find unofficial events and other with the students data.
When making a COUNT() at official events I have no problem, nor do I have a problem when making a COUNT() to unofficial events. My problem is when I make a COUNT(*) when trying to join the two tables.
TABLES:
stu
| ID | stureg | name | 2name | surname | 2surname |
|---|---|---|---|---|---|
| 1 | 500 | Alvina | X | Reyes | Claudia |
| 2 | 506 | Jose | X | Albina | Perez |
| 3 | 585 | Wilt | X | Sharin | Colt |
| 4 | 413 | Hernando | X | Eulogio | Constantino |
| 5 | 713 | Zaida | X | India | Bautista |
| 6 | 426 | Eugenia | Melissa | Wilson | Reyes |
attendance
| ID | groupnum | IDevent | stureg |
|---|---|---|---|
| 1 | 18 | 6 | 500 |
| 2 | 18 | 6 | 585 |
| 3 | 18 | 5 | 500 |
| 4 | 18 | 5 | 413 |
| 5 | 18 | 5 | 713 |
| 6 | 18 | 4 | 500 |
| 7 | 18 | 4 | 462 |
| 8 | 18 | 3 | 506 |
| 9 | 17 | 3 | 585 |
| 10 | 17 | 3 | 462 |
| 11 | 17 | 2 | 500 |
| 12 | 17 | 2 | 585 |
| 13 | 17 | 2 | 413 |
| 14 | 17 | 1 | 506 |
| 15 | 17 | 1 | 413 |
| 16 | 17 | 1 | 713 |
attendance_extra
| ID | groupnum | IDevent | stureg |
|---|---|---|---|
| 1 | 18 | 1 | 500 |
| 2 | 18 | 1 | 506 |
| 3 | 18 | 1 | 585 |
This is the query of official events
SELECT
attendance.stureg, count(*) AS times
,stu.name AS name
,stu.2nameAS 2nameAS
,stu.surname AS surname
,stu.2surname AS 2surname
,stu.stureg AS stureg
FROM
attendance
INNER JOIN stu ON attendance.stureg = stu.stureg
WHERE
attendance.groupnum = 18
GROUP BY
attendance.stureg
HAVING COUNT(*)>0
ORDER BY times DESC;
This is the query of un official events
SELECT
attendance_extra.stureg, count(*) AS times
,stu.name AS name
,stu.2nameAS 2nameAS
,stu.surname AS surname
,stu.2surname AS 2surname
,stu.stureg AS stureg
FROM
attendance_extra
INNER JOIN stu ON attendance_extra.stureg = stu.stureg
WHERE
attendance_extra.groupnum = 18
GROUP BY
attendance_extra.stureg
HAVING COUNT(*)>0
ORDER BY times DESC;
The Query that i try to use is that doesnt work for it was planned
SELECT
attendance.stureg, count(*) AS times
,stu.name AS name
,stu.2nameAS 2nameAS
,stu.surname AS surname
,stu.2surname AS 2surname
,stu.stureg AS stureg
FROM
attendance
INNER JOIN stu ON attendance.stureg = stu.stureg
WHERE
attendance.groupnum = 18
GROUP BY
attendance.stureg
HAVING COUNT(*)>0
UNION (
SELECT
attendance_extra.stureg, count(*) AS times
,stu.name AS name
,stu.2nameAS 2nameAS
,stu.surname AS surname
,stu.2surname AS 2surname
,stu.stureg AS stureg
FROM
attendance_extra
INNER JOIN stu ON attendance_extra.stureg = stu.stureg
WHERE
attendance_extra.groupnum = 18
GROUP BY
attendance_extra.stureg
HAVING COUNT(*)>0
)
ORDER BY times DESC;
The individual Querys work perfectly, it counts the duplicated stureg values, but when i use the last query it only merges the tables, but doesn't count the data, it just add rows at the end
First Query:
| ID | times | stureg | name |
|---|---|---|---|
| 1 | 4 | 500 | Alvina X Reyes Claudia |
| 2 | 3 | 413 | Hernando X Eulogio Constantino |
| 3 | 3 | 585 | Wilt X Sharin Colt |
| 4 | 2 | 713 | Zaida X India Bautista |
| 5 | 2 | 462 | Eugenia Melissa Wilson Reyes |
| 6 | 2 | 506 | Jose X Albina Perez |
When im trying to merge the data:
| ID | times | stureg | name |
|---|---|---|---|
| 1 | 4 | 500 | Alvina X Reyes Claudia |
| 2 | 3 | 413 | Hernando X Eulogio Constantino |
| 3 | 3 | 585 | Wilt X Sharin Colt |
| 4 | 2 | 713 | Zaida X India Bautista |
| 5 | 2 | 462 | Eugenia Melissa Wilson Reyes |
| 6 | 2 | 506 | Jose X Albina Perez |
| 7 | 1 | 500 | Alvina X Reyes Claudia |
| 8 | 1 | 506 | Jose X Albina Perez |
| 9 | 1 | 585 | Wilt X Sharin Colt |
So... what is the query you should apply for this to work as I have planned?