First of all please CC me since I'm no longer a member. I had to unsubscribe
since I receive too much already... Sorry about that.
I think this is just me and my not so good understanding of the GROUP BY
function.
What happens is that it returns less lines than I expect it too. Perhaps it
has something to do with this line I found in the manual which I don't
understand fully probably.....
<manual quote>
MySQL has extended the use of GROUP BY. You can use columns or calculations
in the SELECT expressions that don't appear in the GROUP BY part. This stands
for any possible value for this group. You can use this to get better
performance by avoiding sorting and grouping on unnecessary items. For
example, you don't need to group on customer.name in the following query:
mysql> select order.custid,customer.name,max(payments)
from order,customer
where order.custid = customer.custid
GROUP BY order.custid;
In ANSI SQL, you would have to add customer.name to the GROUP BY clause. In
MySQL, the name is redundant if you don't run in ANSI mode.
Don't use this feature if the columns you omit from the GROUP BY part aren't
unique in the group! You will get unpredictable results.
</manual quote>
first the two tables
mysql> describe Author;
+-----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+----------------+
| ID | int(10) unsigned | | PRI | NULL | auto_increment |
| FirstName | varchar(40) | YES | | NULL | |
| LastName | varchar(60) | YES | | NULL | |
+-----------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> select * from Author;
+----+-------------+-----------+
| ID | FirstName | LastName |
+----+-------------+-----------+
| 1 | Ferry | van Steen |
| 2 | Erik | Zonneveld |
| 3 | Abdel-Karim | Ghribi |
| 4 | Miranda | van Loon |
+----+-------------+-----------+
mysql> describe News;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| ID | int(10) unsigned | | PRI | NULL | auto_increment |
| news | text | YES | | NULL | |
| date | date | YES | | NULL | |
| AID | int(10) unsigned | | | 0 | |
+-------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> select * from News;
+----+---------------------------------------------------------------------------------+------------+-----+
| ID | news
| date | AID |
+----+---------------------------------------------------------------------------------+------------+-----+
| 1 | NAC heeft Ajax verslagen met een mooie 1-3
| 2000-06-06 | 3 |
| 2 | Irak bombadeert Afghanistan na ongelukje met vliegtuig
| 2001-01-22 | 1 |
| 3 | Politiebericht: veel fiesten gestolen in januari vorig jaar
| 2001-03-09 | 2 |
| 4 | Financiele markt IT keldert nog steeds
| 2001-05-09 | 4 |
| 5 | Salarissen in Ziekenhuizen blijven stijgen, zorg personeel heeft niks
te klagen | 2001-04-05 | 4 |
+----+---------------------------------------------------------------------------------+------------+-----+
So far so good. What I want is to select all authors with their news so:
This does what I expect it to, it returns 5 rows
mysql> select * from Author, News where AID=Author.ID;
+----+-------------+-----------+----+---------------------------------------------------------------------------------+------------+-----+
| ID | FirstName | LastName | ID | news
| date | AID
|
+----+-------------+-----------+----+---------------------------------------------------------------------------------+------------+-----+
| 3 | Abdel-Karim | Ghribi | 1 | NAC heeft Ajax verslagen met een mooie
1-3 | 2000-06-06 | 3
|
| 1 | Ferry | van Steen | 2 | Irak bombadeert Afghanistan na
ongelukje met vliegtuig | 2001-01-22 | 1
|
| 2 | Erik | Zonneveld | 3 | Politiebericht: veel fiesten gestolen
in januari vorig jaar | 2001-03-09 | 2
|
| 4 | Miranda | van Loon | 4 | Financiele markt IT keldert nog steeds
| 2001-05-09 | 4
|
| 4 | Miranda | van Loon | 5 | Salarissen in Ziekenhuizen blijven
stijgen, zorg personeel heeft niks te klagen | 2001-04-05 | 4
|
+----+-------------+-----------+----+---------------------------------------------------------------------------------+------------+-----
Now what I want to do is to select this + a column which shows the number of
items the reporter has submitted so i did:
mysql> select *, count(AID) Submissions from Author, News where
News.AID=Author.ID group by Author.ID;
+----+-------------+-----------+----+-------------------------------------------------------------+------------+-----+-------------+
| ID | FirstName | LastName | ID | news
| date | AID | Submissions |
+----+-------------+-----------+----+-------------------------------------------------------------+------------+-----+-------------+
| 1 | Ferry | van Steen | 2 | Irak bombadeert Afghanistan na
ongelukje met vliegtuig | 2001-01-22 | 1 | 1 |
| 2 | Erik | Zonneveld | 3 | Politiebericht: veel fiesten gestolen
in januari vorig jaar | 2001-03-09 | 2 | 1 |
| 3 | Abdel-Karim | Ghribi | 1 | NAC heeft Ajax verslagen met een mooie
1-3 | 2000-06-06 | 3 | 1 |
| 4 | Miranda | van Loon | 4 | Financiele markt IT keldert nog steeds
| 2001-05-09 | 4 | 2 |
+----+-------------+-----------+----+-------------------------------------------------------------+------------+-----+-------------+
Ok this returns nicely that Miranda has submitted 2 news items however it
only shows 1!!! of the news items instead of both of them. Now I'm really
curious why this happens. If I drop the count(AID) and group by I'll have 5
results. AFAIK group is only supposed to make them part of a group so the
count will function on those groups individually not drop lines from the
result or am I mistaken here? I'm really curious about why this is happening.
Please enlighten me :-)
Kind regards,
Ferry van Steen
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
