quote:

---

Originally posted by darius_sutherland

Hi.

Pulling my hair out a little. I have 2 tables and I want to do a sum of row in 1 table divided by the count from another table. Tables can be linked on an ID

So.

I'll just send the gobbledegook query...

SELECT SUM(A.Qty) *1.0/ (SELECT COUNT(B.IssueID)
FROM Issues WHERE (ProductID = 13) AND (DATEPART(year, CoverDate) = 2008) AND (DATEPART(month, CoverDate) = 2) GROUP BY ProductID)
FROM v2Subrun AS A CROSS JOIN Issues AS B WHERE (A.ProductID = 13) AND (DATEPART(year, B.CoverDate) = 2008) AND (DATEPART(month,B.CoverDate) = 2)

Doesn't work. Im all confused...

---

quote:

---

Originally posted by darius_sutherland

Cheers.

Wasn't really thinking or staring too much. Just did it, maybe not the best way but will keep yours in mind if all goes pear chaped...

SELECT SUM(v2Subrun.Qty) AS Expr1, Issues_1.CoverDate, SUM(v2Subrun.Qty) / (SELECT COUNT(IssueID) AS Expr1
FROM Issues WHERE (ProductID = 13) AND (DATEPART(Year, CoverDate) = 2008) AND (DATEPART(Month, CoverDate) = 2)
GROUP BY ProductID) AS Expr2
FROM v2Subrun INNER JOIN
Issues AS Issues_1 ON v2Subrun.IssueID = Issues_1.IssueID
WHERE (v2Subrun.ProductID = 13) AND (DATEPART(month, Issues.CoverDate) = 2) GROUP BY Issues.CoverDate ORDER BY Issues.CoverDate

---