Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Combining Grouped Sum with different Select query

Combining Grouped Sum with different Select query

Combining Grouped Sum with different Select query

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.

RE: Combining Grouped Sum with different Select query

All you need now is to join the 'Inventory' table with  your query. 3eyes

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

Excellent, that worked. What does LEFT JOIN do? How is that different than a normal join? In the past, when I wanted to join two tables, I would list them both after FROM (e.g., FROM Inventory I, Sales S)


RE: Combining Grouped Sum with different Select query

I see, thank you for the article. This will be good to know in the future.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close