have some problem with sql code

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

have some problem with sql code

Author

Topic

vitaly87
Starting Member

13 Posts

Posted - 2009-11-19 : 06:34:53

hello everyone.
i need to find costumers that the total ord in their city begger than the avg in their city.
my tables are
1. ORD (ORDID, ORDERDATE, COMMPLAN, CUSTID, SHIPDATE, TOTAL)
2. CUSTOMER(ZIP, STATE, REPID, PHONE, NAME, CUSTID, REDITLIMIT,
CITY, AREA, ADDRESS, COMMENTS)

i tried that but its wrong


select * from
CUSTOMER,ORD 
GROUP BY CITY
WHERE (CUSTOMER.CUSTID=ORD.CUSTID) AND  (ORD.TOTAL>AVG(TOTAL));

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-11-19 : 06:47:40

Maybe this?


SELECT
	c.[name]
	, o.[ordId]
	, o.[shipDate]
	, o.[total]
FROM
	customer c
	JOIN ord o ON o.[custID] = c.[custId]

	JOIN (
		SELECT
			c2.[city] AS [city]
			, AVG(o2.[total]) AS [av_city_tot]
		FROM
			customer c2
			JOIN ord o2 ON o2.[custId] = c2.[custId]
		GROUP BY
			c2.[city]
		)
		avCit ON avCit.[city] = c.[city]
WHERE
	o.[total] > avCit.[av_city_tot]

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

peterkirubakaran
Starting Member

12 Posts

Posted - 2009-11-19 : 06:54:14

HI..
This is my solution..
check it out...

SELECT CUSTOMER.NAME,ORD.CUSTID,ORD.TOTAL
FROM CUSTOMER INNER JOIN ORD
ON CUSTOMER.CUSTID=ORD.CUSTID
GROUP BY CITY
HAVING ORD.TOTAL>AVG(ORD.TOTAL)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-19 : 07:27:00

quote:
Originally posted by peterkirubakaran

HI..
This is my solution..
check it out...

SELECT CUSTOMER.NAME,ORD.CUSTID,ORD.TOTAL
FROM CUSTOMER INNER JOIN ORD
ON CUSTOMER.CUSTID=ORD.CUSTID
GROUP BY CITY
HAVING ORD.TOTAL>AVG(ORD.TOTAL)

Have you checked it first?
It would throw an error

Madhivanan

Failing to plan is Planning to fail

vitaly87
Starting Member

13 Posts

Posted - 2009-11-19 : 07:43:53

peterkirubakaran ther is an error
ORA-00979: not a GROUP BY expression

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-19 : 07:44:52

quote:
Originally posted by vitaly87

peterkirubakaran ther is an error
ORA-00979: not a GROUP BY expression

This site is for MS SQL Server
Post your oracle questions at www.orafaq.com

Madhivanan

Failing to plan is Planning to fail

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-11-19 : 07:48:20

vitaly87,

Does my solution work for you? I've never used oracle so I don't know how different the derived table expressions would be.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-19 : 07:55:49

quote:
Originally posted by Transact Charlie

vitaly87,

Does my solution work for you? I've never used oracle so I don't know how different the derived table expressions would be.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Column names around square braces wont work in ORACLE

Madhivanan

Failing to plan is Planning to fail

vitaly87
Starting Member

13 Posts

Posted - 2009-11-19 : 08:05:11

so how can i fixed it?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-19 : 08:06:00

quote:
Originally posted by vitaly87

so how can i fixed it?

Remove sqaure braces and try

Madhivanan

Failing to plan is Planning to fail

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-11-19 : 08:50:47

quote:
Originally posted by madhivanan

quote:
Originally posted by Transact Charlie

vitaly87,

Does my solution work for you? I've never used oracle so I don't know how different the derived table expressions would be.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Column names around square braces wont work in ORACLE

Madhivanan

Failing to plan is Planning to fail

Good to know. Maybe I should change my coding style so it is more portable. I guess that I thought it was a good habit to get into as it let me see my columns more easily......

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

vitaly87
Starting Member

13 Posts

Posted - 2009-11-19 : 10:45:33

i tried to do that but it still not working any one knows?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-11-19 : 11:04:10

care to post the code you ran and the error message?

quote:

i tried to do that but it still not working any one knows?

Isn't very descriptive.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

vitaly87
Starting Member

13 Posts

Posted - 2009-11-19 : 12:38:55

i wrote an

SELECT CUSTOMER.NAME,ORD.CUSTID,ORD.TOTAL
FROM CUSTOMER INNER JOIN ORD
ON CUSTOMER.CUSTID=ORD.CUSTID
GROUP BY CUSTOMER.CITY
HAVING ORD.TOTAL>AVG(ORD.TOTAL);

but it still not working
the error is
ORA-00979 :GROUP By

peterkirubakaran
Starting Member

12 Posts

Posted - 2009-11-20 : 00:01:44

quote:
Originally posted by vitaly87

i wrote an

SELECT CUSTOMER.NAME,ORD.CUSTID,ORD.TOTAL
FROM CUSTOMER INNER JOIN ORD
ON CUSTOMER.CUSTID=ORD.CUSTID
GROUP BY CUSTOMER.CITY
HAVING ORD.TOTAL>AVG(ORD.TOTAL);

but it still not working
the error is
ORA-00979 :GROUP By

hi..
this wil give the result(MSSQL)..but isn't a optimized query..

SELECT A.NAME,A.CUSTID,A.TOTAL,A.CITY
FROM (SELECT CUSTOMER.NAME,ORD.CUSTID,ORD.TOTAL,CUSTOMER.CITY
FROM CUSTOMER INNER JOIN ORD
ON CUSTOMER.CUSTID=ORD.CUSTID
GROUP BY CUSTOMER.CITY,ORD.TOTAL,ORD.CUSTID,CUSTOMER.NAME) AS A
JOIN (SELECT CUSTOMER.CITY,AVG(ORD.TOTAL) AS AVERAGE
FROM CUSTOMER INNER JOIN ORD
ON CUSTOMER.CUSTID=ORD.CUSTID
GROUP BY CUSTOMER.CITY) AS TAB
ON A.CITY=TAB.CITY
WHERE A.TOTAL>TAB.AVERAGE

Peets

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-20 : 01:22:33

quote:
Originally posted by peterkirubakaran

quote:
Originally posted by vitaly87

i wrote an

SELECT CUSTOMER.NAME,ORD.CUSTID,ORD.TOTAL
FROM CUSTOMER INNER JOIN ORD
ON CUSTOMER.CUSTID=ORD.CUSTID
GROUP BY CUSTOMER.CITY
HAVING ORD.TOTAL>AVG(ORD.TOTAL);

but it still not working
the error is
ORA-00979 :GROUP By

hi..
this wil give the result(MSSQL)..but isn't a optimized query..

SELECT A.NAME,A.CUSTID,A.TOTAL,A.CITY
FROM (SELECT CUSTOMER.NAME,ORD.CUSTID,ORD.TOTAL,CUSTOMER.CITY
FROM CUSTOMER INNER JOIN ORD
ON CUSTOMER.CUSTID=ORD.CUSTID
GROUP BY CUSTOMER.CITY,ORD.TOTAL,ORD.CUSTID,CUSTOMER.NAME) AS A
JOIN (SELECT CUSTOMER.CITY,AVG(ORD.TOTAL) AS AVERAGE
FROM CUSTOMER INNER JOIN ORD
ON CUSTOMER.CUSTID=ORD.CUSTID
GROUP BY CUSTOMER.CITY) AS TAB
ON A.CITY=TAB.CITY
WHERE A.TOTAL>TAB.AVERAGE

Peets

Again, this is not going to work in ORACLE as it wont allows keyword AS for derived table alias

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-20 : 01:24:21

quote:
Originally posted by vitaly87

i wrote an

SELECT CUSTOMER.NAME,ORD.CUSTID,ORD.TOTAL
FROM CUSTOMER INNER JOIN ORD
ON CUSTOMER.CUSTID=ORD.CUSTID
GROUP BY CUSTOMER.CITY
HAVING ORD.TOTAL>AVG(ORD.TOTAL);

but it still not working
the error is
ORA-00979 :GROUP By

Have you tried Transact Charlie's queries by removing the sqaure braces?

Madhivanan

Failing to plan is Planning to fail

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-11-20 : 03:51:23

Here. I'll spoon feed you:


SELECT
	c.name
	, o.ordId
	, o.shipDate
	, o.total
FROM
	customer c
	JOIN ord o ON o.custID = c.custId

	JOIN (
		SELECT
			c2.city AS city
			, AVG(o2.total) AS av_city_tot
		FROM
			customer c2
			JOIN ord o2 ON o2.custId = c2.custId
		GROUP BY
			c2.city
		)
		avCit ON avCit.city = c.city
WHERE
	o.total > avCit.av_city_tot

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources