0 
CNum  DNum  RNum  Quant  Price
C100  D1    R10   2      8.99
C100  D1    R40   7      9.99
C200  D3    R10   4      16.99
C200  D3    R20   2      15.99
C200  D3    R30   2      17.99
C200  D3    R40   5      19.99
C200  D3    R50   6      18.99
C200  D3    R60   4      19.99
C200  D3    R70   8      15.99
C200  D5    R20   1      8.99
C300  D3    R10   2      16.99
C300  D4    R20   5      22.99
C400  D6    R30   3      4.99
C400  D6    R70   3      2.99
C500  D1    R40   1      9.99
C500  D2    R20   2      23.99
C500  D2    R40   1      24.99
C500  D3    R40   2      19.99
C500  D4    R40   8      23.99
C500  D5    R40   4      8.99
C500  D5    R50   5      8.99
C500  D5    R70   1      9.99
C500  D6    R20   2      1.99
C500  D6    R40   5      3.99

The table above is name Orders. The Query I'm trying to solve is stated as "For each dish ordered from a restaurant, get the dish number(DNum), the restaurant number(RNum), and the total quantity (for that dish ordered from that restaurant)." I can get the two numbers to populate, but am totally unsure of how to add up the quantities, anything I've tried just adds up the Quantities in total. Any ideas?

Here is one of the queries I tried. This actually returned an error:"Your query does not include the specified expression 'DNum' as part of an aggregate function.'

SELECT Ord1.DNum, Ord2.DNum, SUM(Ord1.Quant + Ord2.Quant) AS TotQuant
FROM Orders AS Ord1, Orders AS Ord2
WHERE (Ord1.RNum = Ord2.RNum)

another thats not working

SELECT Order1.DNum, Order2.DNum, TotQuant
FROM  (SELECT SUM(Order1.Quant + Order2.Quant) AS TotQuant 
                                   FROM Orders AS Order1, Orders AS Order2
                                   WHERE (Order1.RNum = Order2.RNum)
                                   AND (Order1.DNum = Order2.DNum))

and one more 

SELECT DISTINCT Ord1.DNum, SUM(Ord1.Quant + Ord2.Quant) AS TotQuant
FROM Orders AS Ord1, Orders AS Ord2
WHERE (Ord1.RNum = Ord2.RNum)
AND (Ord1.DNum = Ord2.DNum)
Improve this question
3
  • 2
    Can you show some of the queries you have tried and their output? Commented Sep 20, 2017 at 16:39
  • @cosinepenguine posted above Commented Sep 20, 2017 at 17:55
  • 1
    Can you show the expected result? I suspect what you need is to GROUP BY Commented Sep 20, 2017 at 18:26

2 Answers 2

Reset to default
2

If my guess as to what you are trying to do is correct something like this should get you close:

SELECT DNum, RNum, SUM(Quant) AS TotalQuantity
FROM Orders
GROUP BY DNum, RNum
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Ah shoot! You beat me to it! Well played, well played!
1

Ok so some quick comments on what you have tried:

Query 1

SELECT Ord1.DNum, Ord2.DNum, SUM(Ord1.Quant + Ord2.Quant) AS TotQuant
FROM Orders AS Ord1, Orders AS Ord2
WHERE (Ord1.RNum = Ord2.RNum);

This might seem like it should work, but if you think about it it's quite a meaningless query. You are selecting two identical DNum values and SUMming two identical Quant values. A human might be able to understand what you're asking the computer to do, but the computer is perplexed.

Query 2 and Query 3 will not work, primarily because they are similar to the initial query that returns and error. They are slightly different, but essentially you are asking for the wrong thing.

Now here's what you can try:

Introducing the GROUP BY method! Woohoo!

GROUP BY is perfect for this and many other queries! As stated on the w3schools page for it:

The GROUP BY statement is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns.

So a query like this:

SELECT Orders.DNum, Orders.RNum, sum(Orders.Quant) as OrderQuantity
FROM Orders
GROUP BY (Orders.DNum, Orders.RNum);

To deconstruct this a little bit:

  1. This selects the columns you want to display, and the aggregate of the Orders.Quant column you want the sum of.
  2. Then, you group by the DNum, which is then grouped by RNum to get you the sum you are looking for.

Hope it helps!

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.