CODEWITHSUNDEEP
phpmysql
StudioTime
StudioTime

Reputation: 23959

Select within a select based on results MySQL

I have a table e.g.

Artist     Title      Song Key
A          Title A    A
A          Title A    B
A          Title A    F
B          Title B    A

I want to return each individual song but also show how many versions there are of that song e.g. Ideal results would be:

Artist     Title       How_many
A          Title A     3
B          Title B     1

This is what I'm trying but the count counts all tracks and not just from the current line:

SELECT *, 
       ( select count(*) 
           from tracks 
          where artist=artist 
            AND title=title ) as How_many 
  from tracks
 where easytosing='Yes'
 group by title, artist
 order by track_id desc

Is this possible using a nested query or is there a better way?

Upvotes: 0

Views: 89

Answers (5)

Sarma Mullapudi
Sarma Mullapudi

Reputation: 110

Just simply you can execute the following query with group by

  select Artist,Title,count(Song_Key) as How_many
  from Tracks
  group by Artist,Title

once you execute above query you will get the result as you like.

   ARTIST   TITLE   HOW_MANY
     A      Title A     3
     B      Title B     1

2 rows returned in 0.00 seconds

Upvotes: 0

shola
shola

Reputation: 704

SELECT t.*, how_many
        from tracks t
inner join 
( select title,artist, count(*)  ,how_many
           from tracks 
          group by artist,title ) as temp
temp.title=t.title and temp.artist=t.artist
 where easytosing='Yes'

Upvotes: 1

user1646111
user1646111

Reputation:

try this:

SELECT Artist, title, count(*) as How_many 
FROM tracks
WHERE easytosing='Yes'
GROUP BY Artist,Title

Upvotes: 1

Praveen Prasannan
Praveen Prasannan

Reputation: 7123

SELECT Artist,Title, COUNT(*) FROM TABLE1 GROUP BY Artist,Title

SAMPLE FIDDLE

Upvotes: 0

Joe Taras
Joe Taras

Reputation: 15379

In the specific case:

SELECT title, artist, count(*)
from tracks
where easytosing='Yes'
group by title, artist

because you get for every artist / title the number of songs.

You don't need of subquery in this case.

Another query you can perform is a query where the main select is based on disk table (I think you have a disk table where you have a foreign key at the artist and a name of your disk) where you get a number of contained song for each artist / disk.

In this way if there are no songs for one disk you get zero value.

Upvotes: -1

Related Questions