Tom:
I see your point, but the group by is necessary so I can walk through all Song Title groups and get the total number of unique versions of that song. If I do this:
SELECT DISTINCT file_details.Title,
file_details_1.CD,
file_details_1.mp3Name,
COUNT(*) AS cnt
FROM file_details LEFT JOIN file_details AS file_details_1
ON file_details.Title = file_details_1.Title
WHERE (((file_details.Type) Like 'Song%')
AND file_details.CD = 'Wasted Tears'
AND file_details_1.Type LIKE 'Song%'
AND file_details_1.FileName NOT LIKE '%_ds.php3'
AND file_details.Title = 'Seems I\'ll')
GROUP BY file_details.Title
ORDER BY file_details_1.Title;
I get this: +------------+--------------+-----------------------+-----+ | Title | CD | mp3Name | cnt | +------------+--------------+-----------------------+-----+ | Seems I'll | Wasted Tears | mp3/mp3s/seemsill.mp3 | 6 | +------------+--------------+-----------------------+-----+
COUNT = 6; And, it's distinct. Watch what happens when I do a row listing without the grouping;
SELECT DISTINCT file_details.Title,
file_details_1.CD,
file_details_1.mp3Name
FROM file_details LEFT JOIN file_details AS file_details_1
ON file_details.Title = file_details_1.Title
WHERE (((file_details.Type) Like 'Song%')
AND file_details.CD = 'Wasted Tears'
AND file_details_1.Type LIKE 'Song%'
AND file_details_1.FileName NOT LIKE '%_ds.php3'
AND file_details.Title = 'Seems I\'ll')
ORDER BY file_details_1.Title;
+------------+-----------------+-------------------------------------+
| Title | CD | mp3Name |
+------------+-----------------+-------------------------------------+
| Seems I'll | Fear of Success | mp3/mp3s/SeemsIll20031029.mp3 |
| Seems I'll | n/a | mp3/mp3s/SeemsIllUnpluggedMixed.mp3 |
| Seems I'll | Wasted Tears | mp3/mp3s/seemsill.mp3 |
+------------+-----------------+-------------------------------------+So, here the listing is the group of records I want and there are 3, and it's distinct.
Why when I put the group on this query (which is what I need) does it double the count?
I think it's a bug.
Best Regards, Van
================================================================= http://www.dedserius.com/ - Linux rocks!!! =================================================================
Tom Crimmins wrote:
On Sunday, February 27, 2005 19:20, Van wrote:
Hi Van,
Greetings:
I've got a table that has the following fields that are relevant to my self-join: FileName | varchar(100) | | MUL | | Title | varchar(45) | | MUL | | Type | varchar(20) | | | HTML | mp3Name | varchar(100) | | | | CD | varchar(25) | | | |
Here are the relevant values for the fields for the song Seems I'll: +------------------------------------+------------+--------+---------- FileName | Title | Type | mp3Name | CD | +------------------------------------+------------+--------+---------- mp3/php3/seemsill.php3 | Seems I'll | Song | mp3/mp3s/seemsill.mp3 | Wasted Tears | mp3/php3/SeemsIllUnplugged.php3 | Seems I'll | Song | mp3/mp3s/SeemsIllUnpluggedMixed.mp3 | n/a | mp3/php3/SeemsIll20031029.php3 | Seems I'll | Song | mp3/mp3s/SeemsIll20031029.mp3 | Fear of Success | lyrics/seemsill.php3 | Seems I'll | Lyrics | mp3/php3/SeemsIll20031029_ds.php3 | Wasted Tears | mp3/php3/seemsill_ds.php3 | Seems I'll | Song | mp3/mp3s/seemsill.mp3 | Wasted Tears | mp3/php3/SeemsIll20031029_ds.php3 | Seems I'll | Song | mp3/mp3s/SeemsIll20031029.mp3 | Fear of Success | mp3/php3/SeemsIllUnplugged_ds.php3 | Seems I'll | Song | mp3/mp3s/SeemsIllUnpluggedMixed.mp3 | n/a | +------------------------------------+------------+--------+----------
Here is the query in question (I'm trying to get the count of all versions of Seems I'll songs, which was originally on the CD Wasted Tears, so I can display the other versions, including the one on Wasted Tears {mp3/mp3s/seemsill.mp}): SELECT file_details.Title, file_details.Type, file_details_1.CD, file_details_1.mp3Name, COUNT(*) AS cnt FROM file_details LEFT JOIN file_details AS file_details_1 ON file_details.Title = file_details_1.Title WHERE (((file_details.Type) Like 'Song%') AND file_details.CD = 'Wasted Tears' AND file_details_1.Type LIKE 'Song%' AND file_details_1.FileName NOT LIKE '%_ds.php3' AND file_details.Title = 'Seems I\'ll') GROUP BY file_details.Title, file_details.Type, file_details_1.CD, file_details_1.mp3Name ORDER BY file_details_1.Title;
But, oddly here is the result of this query: +------------+------+-----------------+------------------------- Title | Type | CD | mp3Name | cnt | +------------+------+-----------------+------------------------- Seems I'll | Song | Fear of Success | mp3/mp3s/SeemsIll20031029.mp3 | 2 | Seems I'll | Song | n/a | mp3/mp3s/SeemsIllUnpluggedMixed.mp3 | 2 | Seems I'll | Song | Wasted Tears | mp3/mp3s/seemsill.mp3 | 2 | +------------+------+-----------------+-------------------------
The count should be 3, right? What gives?
The cnt field looks to be correct here based on your data. The count here is the number of results in that grouping not the total number of rows returned. If you remove the group by clause from your query, you should see 6 rows returned (2 of each).
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
