SQL Code Help...Important!
-
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.
-
Hi
If i am not wrong those are not databases but tables residing in a single database. If so
SELECT SUM(C.SALES) AS SALES, D.DISTRICTNAME, R.REGIONNAME
FROM CUSTOMER C INNER JOIN CITY C1 ON C.CITYID = C1.CITYID
INNER JOIN DISTRICT D ON D.DISTRICTID = C1.DISTRICTID
INNER JOIN REGION R ON R.REGIONID = D.REGIONID
GROUP BY R.REGIONNAME, D.DISTRICTNAME
ORDER BY SALES
Lucky -
Just to be more specific, I have four tables. The group by function will not work since I am not using an aggregate function. Each customer only exists once so I do not need to SUM. I have the following code but am getting a syntax error:
SELECT C.CUSTOMER_NAME, C.SALES
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, C.SALES;
Thanx.
-
Check your column names in joins & order by clause
If this doesn't fix problem , then what is the syntax error, and what are the datatypes of each column?
SELECT C.CUSTOMER_NAME, C.SALES
FROM CUSTOMER C INNER JOIN CITY C1 ON C.CITY_ID=C1.CITY_ID
INNER JOIN DISTRICT D ON D.DISTRICTID=C1.DISTRICTID
INNER JOIN REGION R ON R.REGIONID=D.REGIONID
ORDER BY R.REGIONID, D.DISTRICTID, C.SALES;
-
talk about covering the bases!!!
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=35674
and the main advice of "supply the real DDL, sample input and expected results".....applies as the best advice everywhere!!!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply