sql help

[Nikki]

SELECT OrdD1.OrderID AS OrderID,
SUM(OrdD1.Quantity) AS "Units Sold",
SUM(Ord01.UnitPrice * OrdD1.Quantity) AS Revenue
FROM [Order Details] AS OrdD1
WHERE OrdD1.OrderID in (SELECT DISTINCT OrdD2.OrderID
FROM [Order Details] AS OrdD2
WHERE OrdD2.UnitPrice > $100)
GROUP BY OrdD1.OrderID
HAVING SUM(OrdD1.Quantity) > 100

above is my sql code, but i am having problems, i keep getting the error messege

The column prefix 'Ord01' does not match with a table name or alias name used in the query.

and i cant find out why i still keep getting this error, as its in the table

TIA

[Nikki]

have managed to figure it out, just after i posted it. Theere was a spelling error in the unit price code line DOH!

[Q-Ball]

Nikki - try and format the sql a bit better as i find it helps readability
e.g

SELECT OrdD1.OrderID AS OrderID
,          SUM(OrdD1.Quantity) AS "Units Sold"
,          SUM(Ord01.UnitPrice * OrdD1.Quantity) AS Revenue
FROM [Order Details] AS OrdD1
WHERE OrdD1.OrderID in (SELECT DISTINCT OrdD2.OrderID
                                  FROM [Order Details] AS OrdD2
                                  WHERE OrdD2.UnitPrice > $100)
                                  GROUP BY OrdD1.OrderID
                                  HAVING SUM(OrdD1.Quantity) > 100
                                  )

also, to improved readability, keep everything lower case. some people keep keys word upper case, so you could try that.

[Nikki]

i have always thought the caps for say 'select' wouldnt work in lower case, or that me being a dumb ass and need to try it first lol

[Nikki]

another little problem,

use Pubs
select Pub_name
from Publishers
where EXISTS
(
select * from titles
where titles.pub_id - publishers.pub_id
AND Type = 'business'
)

i am running the sql code but i keep getting a syntax for the 'and' used within the statement? any ideas pleeeeeeeeeeesssseee

[keifster]

"type" is probably a key word. I assume it's supposed to be a fieldname. Try putting the tablename before it (and a dot) eg. titles.type

[TwoPlAnKs]

where titles.pub_id - publishers.pub_id

is that not meant to be a =? if it is meant to be a - then you shouldnt be doing maths on IDs, they might be integer types but they arent numbers (even though they are represented by numbers)

i dont know sql well enough to know if minus returns a value that can become the boolean that where needs, but i imagine it cant.

tbh i'm just being a pedantic cunt and trying to show off that i know big words. its cos the minus should be an equals

[Nikki]

is that not meant to be a =? if it is meant to be a - then you shouldnt be doing maths on IDs, they might be integer types but they arent numbers (even though they are represented by numbers)

erm..... no

i have just managed to fig the problem out, thanks for the replies