CODEWITHSUNDEEP
sqlsql-server
Mark Tickner
Mark Tickner

Reputation: 1053

Counting values from table results

I have a table of data that looks like the following:

     ArtistName    TrackName             TrackID

1    Pendulum      Slam                  6
2    N/A           N/A                   26
3    N/A           N/A                   26
4    N/A           N/A                   26
5    Snow Patrol   Chasing Cars          17
6    Snow Patrol   Chasing Cars          17
7    Rihanna       Love The Way You Lie  4
8    N/A           N/A                   26
9    N/A           N/A                   26
10   Kanye West    Stronger              10
11   Rihanna       Love The Way You Lie  4
12   N/A           N/A                   26
13   N/A           N/A                   26
14   Tinie Tempah  Written In The Stars  8
15   N/A           N/A                   26
16   N/A           N/A                   26
17   Nero          Crush On You          18

etc...

Basically what I'd like to do is count the number of occurrences of each TrackID, and display that in a column. The previous table is created from this query which combines a few other tables:

SELECT Artist_Details.ArtistName, Track_Details.TrackName, Sales_Records.TrackID 
FROM Track_Details 
INNER JOIN Sales_Records ON Track_Details.TrackID = Sales_Records.TrackID 
JOIN Artist_Details ON Track_Details.ArtistID = Artist_Details.ArtistID;

The output format I'd like is:

     ArtistName    TrackName             Track ID    TotalSales

1    Pendulum      Slam                  6           8
2    Tinie Tempah  Written In The Stars  8           5
3    Rihanna       Love The Way You Lie  4           2

And finally, I'd like the value 26 to not be counted and to be ignored and not displayed in the results, with it sorted ascending by TotalSales. And if possible to limit this chart to 10 rows.

Thanks in advance, Mark

Upvotes: 1

Views: 55

Answers (1)

Andomar
Andomar

Reputation: 238078

That looks like a slam dunk for group by:

SELECT top 10 Artist_Details.ArtistName, Track_Details.TrackName, 
      Sales_Records.TrackID, count(Sales_Records.TrackID) as TotalSales
FROM Track_Details 
INNER JOIN Sales_Records ON Track_Details.TrackID = Sales_Records.TrackID 
JOIN Artist_Details ON Track_Details.ArtistID = Artist_Details.ArtistID
WHERE Sales_Records.TrackID <> 26
GROUP BY Artist_Details.ArtistName, Track_Details.TrackName, Sales_Records.TrackID
ORDER BY count(Sales_Records.TrackID) desc

Upvotes: 1

Related Questions