CODEWITHSUNDEEP
sql-servert-sqlsql-server-2008
Gorgeous
Gorgeous

Reputation: 47

Doing a Count on more than a column

I have the following tables:

**Product**  
ProdID  int  
ProdOwner (nvarchar)  
Views int  


**Reviews**  
RevID int  
RevDate datetime  
ProdOwner  
ProdID

If I pass a string like "Jake Bill" (ProdOwner) to my storedproc, I need to get the following output

Total Products Owned: 27  
Total Product Views: 78967  
Total Reviews Received on All Products: 89

How can I write this query? I am using SQL 2008. This is what I did:

CREATE PROCEDURE [dbo].[GetProdCountByOwner]  
(  
   @ProdOwnerName    nvarchar(256)
)  
AS  
SET NOCOUNT ON  

SELECT COUNT(p.ProdID),  Count(p.Views), Count(r.RevID)  FROM Product p join Review r
on p.ProdID = r.ProdID
WHERE p.ProdOwner = @ProdOwnerName
GO

Upvotes: 0

Views: 137

Answers (2)

gbn
gbn

Reputation: 432210

Assuming that you are getting too high results (that is, the highest count is given for all 3)

SELECT
    COUNT(DISTINCT p.ProdID),  SUM(p.Views), Count(DISTINCT r.RevID)
FROM
    Product p LEFT join Review r on p.ProdID = r.ProdID
WHERE
    p.ProdOwner = @ProdOwnerName

Upvotes: 0

The Scrum Meister
The Scrum Meister

Reputation: 30111

SELECT COUNT(*) AS [Total Products Owned],
    SUM(p.Views) AS [Total Product Views],
    (SELECT COUNT(*)
      FROM Product p JOIN Reviews r ON p.ProdID = r.ProdID
      WHERE p.ProdOwner = 'Jake Bill'
    ) AS [Total Reviews Received on All Products]
FROM Product p
WHERE p.ProdOwner = 'Jake Bill'

Upvotes: 1

Related Questions