Query Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query Help

Author

Topic

mdavis
Starting Member

4 Posts

Posted - 2009-06-29 : 12:46:30

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.

Mark Davis
J.D. Cloud & Co.
www.jdcloud.com

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-06-29 : 13:12:46

[code]SELECT I.CUSIP, I.PurchDate, I.OrigUnits, I.OrigPrice
,COALESCE(D.TotalSold, 0) AS TotalSold
FROM Inventory I
LEFT JOIN
(
SELECT S.CUSIP, S.PurchDate, SUM(S.UnitsSold) As TotalSold
FROM Sales S
GROUP BY S.CusIP, S.PurchDate
) D
ON I.CUSIP = D.CUSIP
AND I.PurchDate = D.PurchDate
[/code]

mdavis
Starting Member

4 Posts

Posted - 2009-06-30 : 09:36:04

Thanks. Would this query work if not on SQL server?

Mark Davis
J.D. Cloud & Co.
www.jdcloud.com

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-06-30 : 09:40:00

quote:
Originally posted by mdavis

Thanks. Would this query work if not on SQL server?

Mark Davis
J.D. Cloud & Co.
www.jdcloud.com

Most likely it should work

Madhivanan

Failing to plan is Planning to fail

mdavis
Starting Member

4 Posts

Posted - 2009-06-30 : 10:20:18

Hm, because when I run it I get an "undefined function 'COALESCE' in expression" error.

Mark Davis
J.D. Cloud & Co.
www.jdcloud.com

X002548
Not Just a Number

15586 Posts

Posted - 2009-06-30 : 10:57:00

quote:
Originally posted by mdavis

Hm, because when I run it I get an "undefined function 'COALESCE' in expression" error.

Mark Davis
J.D. Cloud & Co.
www.jdcloud.com

Let's play guess the platform!!!!!

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

mdavis
Starting Member

4 Posts

Posted - 2009-06-30 : 11:08:43

quote:
Originally posted by X002548

quote:
Originally posted by mdavis

Hm, because when I run it I get an "undefined function 'COALESCE' in expression" error.

Mark Davis
J.D. Cloud & Co.
www.jdcloud.com

Let's play guess the platform!!!!!

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

I take it this an error unique to MS Access? I'm new to this.

Mark Davis
J.D. Cloud & Co.
www.jdcloud.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources