how to group

[beginnerboy]

Hi
I have a table like this
state sales
oh 200.00
DE 700.00
NJ 800.00
NJ 500.00
NY 2000.00
IN 100.00
.. ....
I need to get the results like

state sale
NJ 1300
NY 2000
other 1000

I need to show top five states with high sales record and sales of other remaining states as one group. There must be six groups only.

 

[TheJon]

Without testing, the following code comes to mind:

[tt]
select top 5
state,
sales
from
myTable
order by
sales desc
union all
select
'others' as state,
sum(sales) as sales
from
mytable
where
state not in (select top 5 state from myTable order by sales desc)

[/tt]

|_ |_  _  | _  _
|, | )(/, |(_)| )
          '

 

[donutman]

I think this works:
Select case when State='NJ' or State='NY' then State else 'Other' end) as SomeStates, sum(sales) from MyTable group by case when State='NJ' or State='NY' then State else 'Other' end)

Maybe you can say group by SomeStates, I'm not sure. Let us know.
-Karl

 

[donutman]

Oops forgot the ( after the "group by"
-Karl

 

[donutman]

Double oops. I didn't read the last line of your request!
-Karl

 

[TheJon]

oops to me too, you cannot use an order by before a union...

you could populate a temporary table with both of the queries, and select * from that

or, you could make the top 5 query a view and use that in the union, and the where clause for the aggregation as follows:

[tt]create view vw_top_5_states as
select top 5
state,
sales
from
myTable
order by
sales desc

select
state,
sales
from
vw_top_5_states
union all
select
'others' as state,
sum(sales) as sales
from
mytable
where
state not in (select state from vw_top_5_states)[/tt]

|_ |_  _  | _  _
|, | )(/, |(_)| )
          '

 

[beginnerboy]

thanks thejon and donutman I got it .