ORDER BY Aggregate and Grouping

I apologize for this simple question. I'm trying to create a query such tha
t
the results are grouped by 'City' and the cities are returned in decending
order by the Sum(cash).
I'd like the data returned like this:
city nonAgg1 nonAgg2 'sum'
----
name2 'some value' 'some value' 10
name2 'some value' 'some value' 9
name1 'some value' 'some value' 11
name3 'some value' 'some value' 9
name3 'some value' 'some value' 1
I've been using a query like below:
SELECT City, nonAgg1, nonAgg2, SUM(cash) FROM table
GROUP BY City, nonAgg1, nonAgg2
ORDER BY SUM(cash) DESC, City
The (poorly written) query is, of course, ordering the values as such:
city nonAgg1 nonAgg2 'sum'
----
name1 'some value' 'some value' 11
name2 'some value' 'some value' 10
name2 'some value' 'some value' 9
name3 'some value' 'some value' 9
name3 'some value' 'some value' 1
How can I rewrite the query so that it appears in the order I'm trying to
get it in?Hi, Newbie412
You should post DDL and sample data as "CREATE TABLE ..." statements
and "INSERT INTO ... VALUES ..." statements, like this:
CREATE TABLE TheTable (
TheDate datetime,
city varchar(30),
nonAgg1 varchar(20),
nonAgg2 varchar(20),
cash money,
PRIMARY KEY (TheDate, city, nonAgg1, nonAgg2)
)
INSERT INTO TheTable VALUES ('20051220','name1', 'a', 'x', 5)
INSERT INTO TheTable VALUES ('20051221','name1', 'a', 'x', 6)
INSERT INTO TheTable VALUES ('20051220','name2', 'a', 'x', 10)
INSERT INTO TheTable VALUES ('20051220','name2', 'a', 'y', 9)
INSERT INTO TheTable VALUES ('20051220','name3', 'a', 'x', 5)
INSERT INTO TheTable VALUES ('20051221','name3', 'a', 'x', 4)
INSERT INTO TheTable VALUES ('20051220','name3', 'b', 'y', 1)
The following query returns the expected results:
SELECT t.City, nonAgg1, nonAgg2, SUM(cash) SumOfCash
FROM TheTable t INNER JOIN (
SELECT City, SUM(cash) as CityCash
FROM TheTable
GROUP BY City
) x ON t.City=x.City
GROUP BY t.City, x.CityCash, nonAgg1, nonAgg2
ORDER BY CityCash DESC, t.City
Razvan|||Hi
CREATE TABLE #Test (col1 CHAR(1) NOT NULL, col2 INT NOT NULL)
INSERT INTO #Test VALUES ('A',10)
INSERT INTO #Test VALUES ('A',100)
INSERT INTO #Test VALUES ('A',20)
INSERT INTO #Test VALUES ('B',500)
INSERT INTO #Test VALUES ('C',1)
INSERT INTO #Test VALUES ('C',8)
SELECT col1,col2 FROM #test ORDER BY col1 ASC,col2 DESC
DROP TABLE #Test
"Newbie412" <Newbie412@.discussions.microsoft.com> wrote in message
news:6F96CBC0-E42D-460B-8B4B-417E575350DA@.microsoft.com...
>I apologize for this simple question. I'm trying to create a query such
>that
> the results are grouped by 'City' and the cities are returned in decending
> order by the Sum(cash).
> I'd like the data returned like this:
> city nonAgg1 nonAgg2 'sum'
> ----
> name2 'some value' 'some value' 10
> name2 'some value' 'some value' 9
> name1 'some value' 'some value' 11
> name3 'some value' 'some value' 9
> name3 'some value' 'some value' 1
> I've been using a query like below:
> SELECT City, nonAgg1, nonAgg2, SUM(cash) FROM table
> GROUP BY City, nonAgg1, nonAgg2
> ORDER BY SUM(cash) DESC, City
> The (poorly written) query is, of course, ordering the values as such:
> city nonAgg1 nonAgg2 'sum'
> ----
> name1 'some value' 'some value' 11
> name2 'some value' 'some value' 10
> name2 'some value' 'some value' 9
> name3 'some value' 'some value' 9
> name3 'some value' 'some value' 1
> How can I rewrite the query so that it appears in the order I'm trying to
> get it in?|||Newbie412,
Here are solutions for SQL Server 2000 and 2005, if I
understood your requirements correctly.
CREATE TABLE #Test (
City VARCHAR(5) NOT NULL,
nonagg INT,
cash INT NOT NULL
)
INSERT INTO #Test VALUES ('name1',1,6)
INSERT INTO #Test VALUES ('name1',1,1)
INSERT INTO #Test VALUES ('name1',1,4)
INSERT INTO #Test VALUES ('name2',1,10)
INSERT INTO #Test VALUES ('name2',2,4)
INSERT INTO #Test VALUES ('name2',2,5)
INSERT INTO #Test VALUES ('name3',1,9)
INSERT INTO #Test VALUES ('name3',2,1)
-- SQL Server 2000
SELECT
City,
nonagg,
sum(cash) as sumCash
FROM (
SELECT
City,
nonagg,
cash,
(select sum(cash) from #Test as T2
where T2.City = T1.City) as orderKey
from #Test as T1
) AS T
GROUP BY City, orderKey, nonagg
ORDER BY orderKey DESC, City, nonagg
GO
-- SQL Server 2005
;
WITH T(City,nonagg,cash,orderKey) AS (
SELECT
City,
nonagg,
cash,
sum(cash) over (partition by City) as orderKey
from #Test
)
SELECT
City,
nonagg,
sum(cash) as sumCash
FROM T
GROUP BY City, orderKey, nonagg
ORDER BY orderKey DESC, City, nonagg
GO
DROP TABLE #Test
-- Steve Kass
-- Drew University
Newbie412 wrote:

>I apologize for this simple question. I'm trying to create a query such th
at
>the results are grouped by 'City' and the cities are returned in decending
>order by the Sum(cash).
>I'd like the data returned like this:
>city nonAgg1 nonAgg2 'sum'
>----
>name2 'some value' 'some value' 10
>name2 'some value' 'some value' 9
>name1 'some value' 'some value' 11
>name3 'some value' 'some value' 9
>name3 'some value' 'some value' 1
>I've been using a query like below:
>SELECT City, nonAgg1, nonAgg2, SUM(cash) FROM table
>GROUP BY City, nonAgg1, nonAgg2
>ORDER BY SUM(cash) DESC, City
>The (poorly written) query is, of course, ordering the values as such:
>city nonAgg1 nonAgg2 'sum'
>----
>name1 'some value' 'some value' 11
>name2 'some value' 'some value' 10
>name2 'some value' 'some value' 9
>name3 'some value' 'some value' 9
>name3 'some value' 'some value' 1
>How can I rewrite the query so that it appears in the order I'm trying to
>get it in?
>