MySQL Forums
Forum List  »  Newbie

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.

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.