Combining Grouped Sum with different Select query
Combining Grouped Sum with different Select query
(OP)
Table Sales
CUSIP PurchDate SaleDate UnitsSold Proceeds
74112E109 08/05/08 4/17/09 5,950 8,291.04
74112E109 08/05/08 4/20/09 3,365 4,996.90
04648X107 10/08/08 4/20/09 7,500 569.98
04648X107 10/08/08 4/20/09 192,500 12,377.42
04648X107 10/09/08 4/20/09 7,500 482.23
04648X107 10/09/08 4/20/09 142,500 11,256.70
Table Inventory
CUSIP PurchDate OrigUnits OrigPrice
74112E109 08/05/08 15,068 3.3025
816288104 08/08/07 50,300 0.772499
04648X107 10/08/08 200,000 1.485
04648X107 10/09/08 200,000 1.25
023164106 12/14/00 16,500 0.58
023164106 06/05/03 206,786 0.723998
023164106 06/05/03 280,896 0.7241
What I would like to do is run a SELECT query that will display each of the Inventory columns, plus an additional column AS TotalSold which is equal to the sum of the total units sold for each row of the inventory. My ouput would look like this:
Table Inventory
CUSIP PurchDate OrigUnits OrigPrice TotalSold
74112E109 08/05/08 15,068 3.3025 9,315
816288104 08/08/07 50,300 0.772499 0
04648X107 10/08/08 200,000 1.485 200,000
04648X107 10/09/08 200,000 1.25 150,000
023164106 12/14/00 16,500 0.58 0
023164106 06/05/03 206,786 0.723998 0
023164106 06/05/03 280,896 0.7241 0
From the Sales table, I've been able to put together a query that will give me the TotalSold.
SELECT Sales.PurchDate, Sales.CUSIP, Sum(Sales.UnitsSold) AS TotalSold
FROM Sales
GROUP BY Sales.PurchDate, Sales.CUSIP;
I'm struggling with finding a way to get the output I'm looking for though.
CUSIP PurchDate SaleDate UnitsSold Proceeds
74112E109 08/05/08 4/17/09 5,950 8,291.04
74112E109 08/05/08 4/20/09 3,365 4,996.90
04648X107 10/08/08 4/20/09 7,500 569.98
04648X107 10/08/08 4/20/09 192,500 12,377.42
04648X107 10/09/08 4/20/09 7,500 482.23
04648X107 10/09/08 4/20/09 142,500 11,256.70
Table Inventory
CUSIP PurchDate OrigUnits OrigPrice
74112E109 08/05/08 15,068 3.3025
816288104 08/08/07 50,300 0.772499
04648X107 10/08/08 200,000 1.485
04648X107 10/09/08 200,000 1.25
023164106 12/14/00 16,500 0.58
023164106 06/05/03 206,786 0.723998
023164106 06/05/03 280,896 0.7241
What I would like to do is run a SELECT query that will display each of the Inventory columns, plus an additional column AS TotalSold which is equal to the sum of the total units sold for each row of the inventory. My ouput would look like this:
Table Inventory
CUSIP PurchDate OrigUnits OrigPrice TotalSold
74112E109 08/05/08 15,068 3.3025 9,315
816288104 08/08/07 50,300 0.772499 0
04648X107 10/08/08 200,000 1.485 200,000
04648X107 10/09/08 200,000 1.25 150,000
023164106 12/14/00 16,500 0.58 0
023164106 06/05/03 206,786 0.723998 0
023164106 06/05/03 280,896 0.7241 0
From the Sales table, I've been able to put together a query that will give me the TotalSold.
SELECT Sales.PurchDate, Sales.CUSIP, Sum(Sales.UnitsSold) AS TotalSold
FROM Sales
GROUP BY Sales.PurchDate, Sales.CUSIP;
I'm struggling with finding a way to get the output I'm looking for though.
Talk To Other Members
RE: Combining Grouped Sum with different Select query
All you need now is to join the 'Inventory' table with your query.
PS: Good luck with your assignment!
.
----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb
RE: Combining Grouped Sum with different Select query
CODE
FROM Inventory I
LEFT JOIN Sales S ON I.CUSIP = S.CUSIP AND I.PurchDate = S.PurchDate
GROUP BY I.CUSIP, I.PurchDate, I.OrigUnits, I.OrigPrice
Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?
RE: Combining Grouped Sum with different Select query
Thanks.
RE: Combining Grouped Sum with different Select query
http:/
Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?
RE: Combining Grouped Sum with different Select query