CODEWITHSUNDEEP
sqlsqliteselectgroup-by
testCoder
testCoder

Reputation: 7385

select list of albums of artist and display for every album tracks count which have requested artist and album in list

I need to make complicated query. I have table:

CREATE TABLE IF NOT EXISTS tracks
     ( id INTEGER PRIMARY KEY, path TEXT,
     title TEXT, artist TEXT,
     album TEXT, genre TEXT, 
    duration INTEGER, rating INTEGER );

Sory for dirty title of question but i don't understand how to explain it more shortly.

Question: How to display list of albums of requested artist and for each displayed album calculate count of tracks which have requested artist in current album in single query.

I have tried something like this:

SELECT albtbl.album as album, albtbl.artist as artist, 
( SELECT count(*) FROM trackstbl WHERE trackstbl.artist = albtbl.aritst ) as tracksCount 
FROM ( SELECT tbl.album as album, tbl.artist as artist, count(title) as tracksCount FROM tracks as tbl  WHERE tbl.artist = 'requested_artist'
 GROUP BY tbl.album ) as albtbl, FROM tracks as trackstbl ORDER BY album ASC

but it not compiled:

SQLiteException: near "FROM": syntax error:

Upvotes: 1

Views: 23814

Answers (4)

Alekhya Reddy
Alekhya Reddy

Reputation: 11

We can achieve this with the help of sub-query as well below is the example:

SELECT DISTINCT album, artist,
    (SELECT COUNT(*) 
     FROM tracks AS Sub_Track
     WHERE Sub_Track.album = Main_Track.album AND Sub_Track.artist = Main_Track.artist) AS Track_Count
FROM tracks AS Main_Track
WHERE artist = 'requested_artist'
ORDER BY album ASC;

The above query SELECTS distinct album & artist pairs for a requested artist, & then it uses a sub-query to get the count of number of tracks for each album & artist combinations.

Upvotes: 0

Rikki
Rikki

Reputation: 3528

It's not that much complicated however it's hard to read. I saw the error on the first sight. Here:

as albtbl, FROM tracks

Comma is extra. So change it to this:

as albtbl FROM tracks

Hope it helps.

Upvotes: 0

WKordos
WKordos

Reputation: 2255

select artist, album, count(1) as countTracks
from tracks
where artist = @requestedArtist
group by artist, album

Upvotes: 1

Yogendra Singh
Yogendra Singh

Reputation: 34367

Not sure, why this is complex. Below query should return you the count of track for each album of searches artist:

     select artist, album, count(*) as tracksCount
     from tracks
     where artist = 'requested_artist'
     group by artist, album;

Upvotes: 2

Related Questions