SQL Server 2016 get count from results of two tables

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

Albums
AlbumID   ArtistID
1                1
2                1
3                2
4                6

Open in new window


Tracks
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
Wayne
Comment
Watch Question

Do more with

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

Commented:
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

Commented:
Cte is a bit much.
Select artistid, count(artistid) as Album_count from albums group by artistid
Author, Web Developer
Top Expert 2009
Commented:
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

Commented:
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