Re: Query with several evaluations on the same column
Posted by:
Jörg Z
Date: June 27, 2016 04:31AM
Thank you for the first answers!
@Peter
<strong>None of the markup methods mentioned in the wiki-link works in this BB. I don't know why.</strong>. So the formatting of the the results is not so good.
Its half of homework: I'm a social worker in a refugee accommodation. My main task is to care. For data management a Mysql database is used, which is used by us in a PHP / HTML environment. On the structure and development of the database, we have no influence. The manufacturer, however, has set me a read access to the tables, so that I can access the data to a MySQL Connector. But we get no further technical support.
Here are my SQL-Statements:
SELECT * , count(*) AS Anzahl FROM persons p
LEFT JOIN events e ON p.persons=e.id_person
GROUP BY p.lastname, e.event_typ
ORDER BY p.persons ASC, e.event_typ ASC
Result:
persons|lastname|firstname|id|event_typ|[event_date]|id_person|Anzahl(=count())
1|Smith|John|1|4|2016-06-2314:04:45|1|2
1|Smith|John|3|5|2016-06-2314:05:02|1|2
1|Smith|John|5|6|2016-06-2314:06:14|1|1
2|Miller|Samantha|2|4|2016-06-2314:04:48|2|1
2|Miller|Samantha|4|5|2016-06-2314:05:06|2|1
2|Miller|Samantha|6|6|2016-06-2314:06:17|2|1
3|Kensington|Henry|7|4|2016-06-2314:07:25|3|1
3|Kensington|Henry|8|6|2016-06-2314:08:20|3|2
4|Dell|Mary,NULL,NULL,NULL,NULL,1
5|McGregor|Gregor,NULL,NULL,NULL,NULL,1
6|London|Billie|9|4|2016-06-2314:10:25|6|1
6|London|Billie|10|5|2016-06-2314:10:25|6|1
6|London|Billie|11|6|2016-06-2314:10:25|6|1
7|Kennedy|Michael,NULL,NULL,NULL,NULL,1
8|Shoemaker|Francis|12|6|2016-06-2314:10:25|8|1
The result is correct in principle. But the result of what appears to be a second or third line for each person is to be appended at the column 'Anzahl' as two additional columns for every person, or if there is no result, to a NULL-type generated or a 0 will be displayed.
This is the result wanted:
persons|lastname|firstname|id|event_typ|[event_date]|id_person|count of event_typ_4|count of event_typ_5|count of event_typ_6
1|Smith|John|1|4|2016-06-2314:04:45|2|2|1
...
3|Kensington|Henry|7|4|2016-06-2314:07:25|1|0|1
...
4|Dell|Mary| | | |0|0|0
...
If I use a WHERE clause
SELECT * , count(e.event_typ) AS Anzahl FROM persons p
LEFT JOIN events e ON p.persons=e.id_person
WHERE e.event_typ=5 or e.event_typ IS NULL
GROUP BY p.lastname, e.event_typ
ORDER BY p.persons ASC, e.event_typ ASC
#WHERE should have the values 4,5 or 6
#The german word 'Anzahl' means number, counting of...
the result is:
persons|lastname|firstname|id|event_typ|event_date|id_person|Anzahl)
1|Smith|John|3|5|2016-06-23 14:05:02|1|2
2|Miller|Samantha|4|5|2016-06-23 14:05:06|2|1
4|Dell|Mary, NULL, NULL, NULL, NULL, 0
5|McGregor|Gregor, NULL, NULL, NULL, NULL, 0
6|London|Billie|10|5|2016-06-23 14:10:25|6|1
7|Kennedy|Michael|NULL|NULL|NULL|NULL|0
I Know, the result is that what I asked for. The counting for the values of event_typ asked is correct.
How can I manage to have the counting results of event-typ value 4,5 and 6 in ONE line (even if they are NULL)?
@Barry (1st entry)
The structure is given by external manufacturer. The example I sent to the forum is simplified with dummy names and dummy date_time entries. In real life each event is identical. At least there is a difference in the event_date field of days normally. Do you think that there should be a separate table for each event?
@Barry (2nd entry)
The insert command was given for two (or more) entries in the same (milli-)second by a tool. Normally there are several seconds, hours, days and sometimes weeks between the entries.
@Plamen
Please look at the @Peter entry.
Hope you can help!
PS: I guess I've made a bit confusion by using the event_date column. This column is completely unimportant for the counting task.