CODEWITHSUNDEEP
sql
Harrison
Harrison

Reputation: 5376

Reference values from other tables

Assume I have two tables, customer and watched.

watched contains the column custID, which is a list of different customer id #'s.

customer contains the columns custID fname and lname.

I would like to count the number of occurrences of each custID in watched, but rather than returning the ID of each customer along with the counts, I would like to return columns custID lname fname and then the count in a new column.

I currently have it working with the return format as custID and then the count, i'm not sure how to reference the customer table to get the output that I would like. I'm very new to learning SQL, sorry if this is trivial. Here's what I have:

SELECT w.custID, count(custID)
    FROM watched w 
GROUP by custID
ORDER BY custID

Upvotes: 1

Views: 52

Answers (1)

Milney
Milney

Reputation: 6417

Using a join? Something like; 

SELECT
    c.custID,
    c.fname,
    c.lname,
    count(*) AS NumberOfWatched
FROM Watched w
INNER JOIN Customer c
    ON w.custID = c.custID
GROUP BY
    c.custID,
    c.fname,
    c.lname

As a side note, please consider naming your columns properly...

I cry when I see abbreviated names for no reason

Upvotes: 2

Related Questions