I am rewriting this question because Gordon Linoff told me it might come off as rude if I edited my other one -- so this isn't a duplicate, just a correction.
I am trying to write a code that will sum the prices of all the orders that come up when I fill out the query. For example, if I enter the ID range 1-60, I want there to be a sum column created that then sums up all the prices of ID's 1-60.
I thought it would be simple enough to just create a SUM(.....) AS Exp 1, but it tells me that there is a problem with the ID and aggregate function.
I want to be able to see the individual prices, as well as a new column with the sum of all these prices. I plan on adding some more columns of data into the table later on.
My current code looks like this:
SELECT table.ID, table.Price, SUM(table.Price) AS Exp 1
FROM table
WHERE table.ID BETWEEN StartID AND EndID
Thank you for any help
You have a concept error with your aggregate statement. When you run this query, the WHERE clause will evaluate first to exclude all IDs that are not between your user specified start and end points. Then, you missed the GROUP BY clause to tell it what needs to be grouped. Eliminate the table.Price field, otherwise you will be getting unique records for each price which is not what you want.
SELECT t.ID, SUM(t.Price) AS Price_Summary
FROM table t
WHERE t.ID BETWEEN StartID AND EndID
GROUP BY t.ID
Also, aliases will help improve readability.
EDIT
I think this might be what you are trying to get to, but I'm still unclear.
SELECT t.ID, SUM(t.Price) AS Price_Summary,
(SELECT SUM(t2.Price) FROM table t2 WHERE t2.ID BETWEEN StartID AND EndID) AS Total_Price
FROM table t
WHERE t.ID BETWEEN StartID AND EndID
GROUP BY t.ID
This will give you a result set with all of the IDs, a sum of the price of everything with that ID, and then a total sum. So it would look something like this:
ID | Price_Summary | Total_Price
1 10 60
2 30 60
3 20 60