Query help...important! - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

Query help...important!

Author

Topic

indianfury
Starting Member

4 Posts

Posted - 2004-05-31 : 06:09:57

Hi,

I have just started working with SQL and need some help:

I have 4 databases:
Customer: Customer Name, CityID, Sales
City: CityID, CityName, DistrictID
District: DistrictID, RegionID
Region: RegionID

I need to write a SQL query that will show all the customers grouped by region and then district and ordred by sales. Any and all help is greatly appreciated.

gates_micro
Starting Member

29 Posts

Posted - 2004-05-31 : 06:42:04

Specify whether you are having 4 tables or 4 databases. Queries work with tables in databases.

SELECT A.CUSTOMERNAME,B.CITYNAME,C.REGIONID,D.DISTRICTID,A.SALES
FROM CUSTOMER A INNER JOIN CITY B ON A.CITYID=B.CITYID
INNER JOIN DISTRICT D ON D.DISTRICTID = B.DISTRICTID
INNER JOIN REGION C ON C.REGIONID=D.REGIONID GROUP BY
A.CUSTOMERNAME,B.CITYNAME,C.REGIONID,D.DISTRICTID,A.SALES

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-05-31 : 11:47:59

The GROUP BY won't work here....as it requires an AGGREGATE function (sum, count, ave, etc) to be included in the SQL command.

My suspicion is that grouping in this instance actually refers to ORDERing...and thus requires use of the ORDER BY command.

also to comply with the stated requirement...the statement should read.
ORDER BY C.REGIONID, D.DISTRICTID, A.SALES

indianfury
Starting Member

4 Posts

Posted - 2004-05-31 : 13:20:06

Sorry for the confusion. I do have 4 tables. The group by will not work. I tried the following but am getting a syntax error:

SELECT C.CUSTOMER_NAME
FROM CUSTOMER C INNER JOIN CITY C1 ON C.CITY_ID=C1.CITY_ID
INNER JOIN DISTRICT D ON D.SALES_DISTRICT=C1.SALES_DISTRICT
INNER JOIN REGION R ON R.SALES_REGION=D.SALES_REGION
ORDER BY R.SALES_REGION, D.SALES_DISTRICT;

gates_micro
Starting Member

29 Posts

Posted - 2004-05-31 : 13:42:43

GROUP BY IS WORKING

gates_micro
Starting Member

29 Posts

Posted - 2004-05-31 : 13:45:56

The fields you have specified in the tables does not match the one which you specified in the query.Solved with only the fields which you have in your tables.

indianfury
Starting Member

4 Posts

Posted - 2004-05-31 : 14:27:44

The names are different because I didn't want to put the entire names up...too many words, did not want to complicate the query. I was under the impression that I could simply replace the field names with the ones I have:

Customer: Customer_Name, City_ID, Sales
City: City_ID, Sales_District
District: Sales_District, Sales_Region
Region: Sales_Region

SELECT C.CUSTOMER_NAME
FROM CUSTOMER C INNER JOIN CITY C1 ON C.CITY_ID=C1.CITY_ID
INNER JOIN DISTRICT D ON D.SALES_DISTRICT=C1.SALES_DISTRICT
INNER JOIN REGION R ON R.SALES_REGION=D.SALES_REGION
ORDER BY R.SALES_REGION, D.SALES_DISTRICT;

This still however create a syntax error. Doesn't make sense.

X002548
Not Just a Number

15586 Posts

Posted - 2004-05-31 : 18:53:58

For the utlimate help...call sqlteam..

Please supply DDL, sample data and expected results...

Answers in minutes....

Brett

8-)

indianfury
Starting Member

4 Posts

Posted - 2004-05-31 : 19:35:11

Best way is e-mail?

gates_micro
Starting Member

29 Posts

Posted - 2004-06-01 : 04:54:53

Using following fields and its working fine.

SELECT A.CUSTOMERNAME
FROM CUSTOMER A INNER JOIN CITY B ON A.CITYID=B.CITYID
INNER JOIN DISTRICT D ON D.DISTRICTID = B.DISTRICTID
INNER JOIN REGION C ON C.REGIONID=D.REGIONID order BY
A.SALES,D.DISTRICTID

Supply the DDL of tables you are using.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources