I have following tables
1) Products (productid, name, description, price)
2) Sales (salesid, productid, buyername, buyeremail, status)
3) ProductViews (viewid, productid)
Now, I need a query that can output as
ProductID ProductName Price TotalViews TotalSales
Help appreciated, thanks.
SELECT p.productid, p.name, p.price, COUNT(pv.viewid) AS totalviews, COUNT(s.salesid) AS totalsales
FROM Products p
LEFT JOIN Sales s ON s.productid = p.productid
LEFT JOIN ProductViews pv ON pv.productid = p.productid
GROUP BY p.productid, p.name, p.price
Extended group by for completeness sake but it could just be p.productid.
You can use subqueries to get the count of views and sales:
SELECT
a.productid,
a.name,
a.price,
(SELECT COUNT(b.viewid)
FROM ProductViews b
WHERE b.productid = a.productid) as TotalViews,
(SELECT COUNT(c.salesid)
FROM Sales c
WHERE c.productid = a.productid) as TotalSales
FROM
Products a
Select p.ProductID, p.ProductName, p.Price, s.c as TotalSales, v.c as TotalViews
FROM Products p
INNER JOIN (select productid, count(*) as c from Sales group by productid) s
ON s.productId = p.productid
INNER JOIN (select productid, count(*) as c from ProductViews group by productid) v
ON p.productId = v.productid
If you have a product that doesn't have a sale or a view you will need to left join instead
SELECT a.productid, a.name, a.price, (
SELECT COUNT( b.viewid )
FROM ProductViews b
WHERE b.productid = a.productid
) AS TotalViews, (
SELECT COUNT( c.salesid )
FROM Sales c
WHERE c.productid = a.productid
) AS TotalSales
FROM products a