CODEWITHSUNDEEP
sqloracle-databasesum
Sh0gun
Sh0gun

Reputation: 931

SQL Nested Where with Sums

I've run into a syntax issue with SQL. What I'm trying to do here is add together all of the amounts paid on each order (paid each) an then only select those that are greater than sum of of paid each for a specific order# (1008). I've been trying to move around lots of different things here and I'm not having any luck.

This is what I have right now, though I've had many different things. Trying to use this simply returns an SQL statement not ended properly error. Any help you guys could give would be greatly appreciated. Do I have to use DISTINCT anywhere here? 

SELECT ORDER#,
    TO_CHAR(SUM(PAIDEACH), '$999.99') AS "Amount > Order 1008"
FROM ORDERITEMS 
GROUP BY ORDER# 
WHERE TO_CHAR > (SUM (PAIDEACH)) 
WHERE ORDER# = 1008;

Upvotes: 0

Views: 1345

Answers (3)

Alex Poole
Alex Poole

Reputation: 191570

Other have commented on some of the things wrong with your query. I'll try to give more explicit hints about what I think you need to do to get the result I think you're looking for.

The problem seems to break into distinct sections, first finding the total for each order which you're close to and I think probably started from:

SELECT ORDER#, SUM(PAIDEACH) AS AMOUNT
FROM ORDERITEMS
GROUP BY ORDER#;

... finding the total for a specific order:

SELECT SUM(PAIDEACH)
FROM ORDERITEMS
WHERE ORDER# = 1008;

... and combining them, which is where you're stuck. The simplest way, and hopefully something you've recently been taught, is to use the HAVING clause, which comes after the GROUP BY and acts as a kind of filter that can be applied to the aggregated columns (which you can't do in the WHERE clause). If you had a fixed amount you could do this:

SELECT ORDER#, SUM(PAIDEACH) AS AMOUNT
FROM ORDERITEMS
GROUP BY ORDER#
HAVING SUM(PAIDEACH) > 5;

(Note that as @Bridge indicated you can't use the column alias, AMOUNT, in the having clause, you have to repeat the aggregation function SUM). But you don't have a fixed value, you want to use the actual total for order 1008, so you need to replace that fixed value with another query. I'll let you take that last step...

Upvotes: 1

JYelton
JYelton

Reputation: 36546

Some versions of SQL regard the hash character (#) as the beginning of a comment. Others use double hyphen (--) and some use both. So, my first thought is that your ORDER# field is named incorrectly (though I can't imagine the engine would let you create a field with that name).

You have two WHERE keywords, which isn't allowed. If you have multiple WHERE conditions, you must link them together using boolean logic, with AND and OR keywords.

You have your WHERE condition after GROUP BY which should be reversed. Specify WHERE conditions before GROUP BY.

One of your WHERE conditions makes no sense. TO_CHAR > (SUM(paideach)): TO_CHAR() is a function which as far as I know is an Oracle function that converts numeric values to strings according to a specified format. The equivalent in SQL Server is CAST or CONVERT.

I'm guessing that you are trying to write a query that finds orders with amounts exceeding a particular value, but it's not very clear because one of your WHERE conditions specifies that the order number should be 1008, which would presumably only return one record.

The query should probably look more like this:

SELECT order,
    SUM(paideach) AS amount
FROM orderitems
GROUP BY order
HAVING amount > 999.99;

This would select records from the orderitems table where the sum of paideach exceeds 999.99.

I'm not sure how order 1008 fits into things, so you will have to elaborate on that.

Upvotes: 2

Bridge
Bridge

Reputation: 30711

I'm not familiar with Oracle, and since it's homework I won't give you the answers, just a few ideas of what I think is wrong.

  • select statement should only have one where statement - can have more than one condition of course, just separated by logical operators (anything that evaluates to true will be included). E.g. : WHERE (column1 > column2) AND (column3 = 100)

  • Group by statements should after WHERE clauses

  • You can't refer to columns you've aliased in the select in the where clause of the same statement by their aliased name. For example this won't work:

    SELECT column1 as hello FROM table1 WHERE hello = 1

  • If there's a group by, the columns you're selecting should be the same as in that statement (or aggregates of those). This page does a better explanation of this than I do.

Upvotes: 0

Related Questions