SQL Server 2016 get count from results of two tables

Wayne Barron
Wayne Barron used Ask the Experts™
I am trying to do a count, to get the number of albums that go with the artist
Here is my table(s)

AlbumID   ArtistID
1                1
2                1
3                2
4                6

Open in new window

TrackID    AlbumID
1               1
2               1
3               1
4               1
5               2
6               2
7               2
8               2
9               6
10             6

Open in new window

What I need is to get the total album ct.
Which in the case above, will be 2 for the Artist 1

When I try to run it with a count(Albums.ArtistID) AS Albums
I get "21"
When I need 2

Running this one
count(distinct Albums.ArtistID) AS Albums
I get 1

Thank you
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ryan ChongSoftware Team Lead

try like this?

;with Albums as
	Select 1 AlbumID, 1 ArtistID union all
	Select 2 AlbumID, 1 union all
	Select 3 AlbumID, 2 union all
	Select 4 AlbumID, 6
Select ArtistID, count(ArtistID) AlbumCount
from Albums
Group By ArtistID

Open in new window

Distinguished Expert 2017

Cte is a bit much.
Select artistid, count(artistid) as Album_count from albums group by artistid
Author, Web Developer
Top Expert 2009
Hey guys.,
So, I tried something and it worked.
I ran a simple count on the tracks table, and got the results I was wanting.
I then added the Albums table to it, and it worked.
This is the code that worked.

SELECT        COUNT(DISTINCT Tracks.AlbumID) AS Albums
FROM            Tracks INNER JOIN
                         Albums ON Tracks.AlbumID = Albums.AlbumID
WHERE        Tracks.LID=1

Open in new window

This will give me the exact count that goes with the given ID.
Distinguished Expert 2017

You can do the same on the album grouping by artistid count albumid
I think I counted the wrong in the example

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial