DISTINCT SUM

[Solid]

DISTINCT SUM

Сложилась непростая ситуация. Есть небольшой запрос:

SELECT 
SQL_CACHE SQL_CALC_FOUND_ROWS 
artist.*, 
album.*, 
COUNT(DISTINCT song.song_id) AS album_song_count, 
COUNT(DISTINCT lyric.lyric_id) AS album_lyric_count, 
SUM(song.song_hits) AS album_hits, 
ROUND(SUM(song2rate.rate_count)/COUNT(song2rate.song_id)) AS album_rate, 
COUNT(DISTINCT comment.comment_id) AS album_comment_count 
FROM artist, album, song 
LEFT JOIN lyric ON lyric.song_id=song.song_id 
LEFT JOIN song2rate ON song2rate.song_id=song.song_id 
LEFT JOIN `comment` ON comment.song_id=song.song_id 
WHERE album.album_id=song.album_id 
AND 
artist.artist_id=album.artist_id 
AND 
album.artist_id=? 
GROUP BY album.album_id 
ORDER BY album.album_year DESC

Всё отлично работает, кроме одного... SUM(song.song_hits) AS album_hits - считает все song_hits... можно ли как-то ограничить суммирование только по song_id?

 

[Necromant]

GROUP BY album.album_id , song.song_id

 

[Solid]

Автор оригинала: Necromant
GROUP BY album.album_id , song.song_id

Не работает.

 

[Necromant]

[sql]
SELECT artist.*, album.*,
SUM(song.song_hits) AS album_hits,
FROM artist, album, song
LEFT JOIN lyric ON lyric.song_id=song.song_id
LEFT JOIN song2rate ON song2rate.song_id=song.song_id
LEFT JOIN `comment` ON comment.song_id=song.song_id
WHERE album.album_id=song.album_id
AND
artist.artist_id=album.artist_id
AND
album.artist_id=12
GROUP BY album.album_id , song.song_id
[/sql]
Упрости и отлаживай

 

[Solid]

Автор оригинала: Necromant
[sql]
SELECT artist.*, album.*,
SUM(song.song_hits) AS album_hits,
FROM artist, album, song
LEFT JOIN lyric ON lyric.song_id=song.song_id
LEFT JOIN song2rate ON song2rate.song_id=song.song_id
LEFT JOIN `comment` ON comment.song_id=song.song_id
WHERE album.album_id=song.album_id
AND
artist.artist_id=album.artist_id
AND
album.artist_id=12
GROUP BY album.album_id , song.song_id
[/sql]
Упрости и отлаживай

Упрощал, отлаживал - никаких результатов. Подозреваю, что решение возможно только через ещё один SELECT между SELECT и FROM.

 

[Necromant]

[sql]
SELECT artist . * , SUM( song.song_hits ) AS album_hits
FROM
(((artist LEFT JOIN album USING(artist_id))
LEFT JOIN song USING(album_id))
LEFT JOIN lyric USING(song_id))
WHERE album.artist_id = 12
GROUP BY album.album_id, song.song_id
[/sql]

 

[Solid]

Автор оригинала: Necromant
[sql]
SELECT artist . * , SUM( song.song_hits ) AS album_hits
FROM
(((artist LEFT JOIN album USING(artist_id))
LEFT JOIN song USING(album_id))
LEFT JOIN lyric USING(song_id))
WHERE album.artist_id = 12
GROUP BY album.album_id, song.song_id
[/sql]

Не, всё оказалось намного проще:

SELECT 
SQL_CACHE SQL_CALC_FOUND_ROWS 
artist.*, 
album.*, 
COUNT(DISTINCT song.song_id) AS album_song_count, 
COUNT(DISTINCT lyric.lyric_id) AS album_lyric_count, 
(SELECT SUM(song.song_hits) FROM song WHERE song.album_id=album.album_id GROUP BY album.album_id) AS album_hits, 
ROUND(SUM(song2rate.rate_count)/COUNT(song2rate.song_id)) AS album_rate, 
COUNT(DISTINCT comment.comment_id) AS album_comment_count 
FROM artist, album, song 
LEFT JOIN lyric ON lyric.song_id=song.song_id 
LEFT JOIN song2rate ON song2rate.song_id=song.song_id 
LEFT JOIN `comment` ON comment.song_id=song.song_id 
WHERE album.album_id=song.album_id 
AND 
artist.artist_id=album.artist_id 
AND 
album.artist_id=2155
GROUP BY album.album_id 
ORDER BY album.album_year DESC

Я просто думал, вдруг есть что-то наподобие [TOPIC].