[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Groupby question

Blasting Cap

3/8/2007 10:16:00 PM

I have a table with the following data in a varchar field (the "10
01-2007" below) and a number (units).

10 01-2007 150
11 01-2007 300
12 01-2007 575
14 01-2007 25
21 01-2007 50000
22 01-2007 25000
31 01-2007 8700
51 01-2007 330

I need to combine the ones with 10, 11 & 12 into one line, number 14 on
one line, 21 & 22 into one line, and 31 & 51 on a single line each
respectively.

The groupby is currently on the first 2 characters of the varchar field,
which gives me 3 separate entries for 10 11 & 12, 2 separate entries on
21 & 22, etc.

Group1 01-2007 150
Group1 01-2007 300
Group1 01-2007 575
Group2 01-2007 25
Group3 01-2007 50000
Group3 01-2007 25000
Group4 01-2007 8700
Group5 01-2007 330


I have to evaluate the first two characters of that field to determine
which group it's supposed to be in.

How do you do a groupby to give you:

Group1 01-2007 1025
Group2 01-2007 25
Group3 01-2007 75000
Group4 01-2007 8700
Group5 01-2007 330


Thanks,

BC
10 Answers

Tom Moreau

3/8/2007 10:50:00 PM

0

Try:

select
'Group' +
case
when left (Col1, 2) in ('10', '11', '12') then '1'
when left (Col1, 2) = '14' then '2'
when left (Col1, 2) in ('21', '22') then '3'
when left (Col1, 2) = '31' then '4'
when left (Col1, 2) = '51' then '5'
end + right (Col1, 7) as TheGroup
, sum (Col2) as Value
from
MyTable
group by
'Group' +
case
when left (Col1, 2) in ('10', '11', '12') then '1'
when left (Col1, 2) = '14' then '2'
when left (Col1, 2) in ('21', '22') then '3'
when left (Col1, 2) = '31' then '4'
when left (Col1, 2) = '51' then '5'
end + right (Col1, 7)

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Blasting Cap" <goober@christian.net> wrote in message
news:Os3XJ9cYHHA.2268@TK2MSFTNGP06.phx.gbl...
I have a table with the following data in a varchar field (the "10
01-2007" below) and a number (units).

10 01-2007 150
11 01-2007 300
12 01-2007 575
14 01-2007 25
21 01-2007 50000
22 01-2007 25000
31 01-2007 8700
51 01-2007 330

I need to combine the ones with 10, 11 & 12 into one line, number 14 on
one line, 21 & 22 into one line, and 31 & 51 on a single line each
respectively.

The groupby is currently on the first 2 characters of the varchar field,
which gives me 3 separate entries for 10 11 & 12, 2 separate entries on
21 & 22, etc.

Group1 01-2007 150
Group1 01-2007 300
Group1 01-2007 575
Group2 01-2007 25
Group3 01-2007 50000
Group3 01-2007 25000
Group4 01-2007 8700
Group5 01-2007 330


I have to evaluate the first two characters of that field to determine
which group it's supposed to be in.

How do you do a groupby to give you:

Group1 01-2007 1025
Group2 01-2007 25
Group3 01-2007 75000
Group4 01-2007 8700
Group5 01-2007 330


Thanks,

BC

loufuki@gmail.com

3/8/2007 11:09:00 PM

0

On Mar 8, 2:15 pm, Blasting Cap <goo...@christian.net> wrote:
> I have a table with the following data in a varchar field (the "10
> 01-2007" below) and a number (units).
>
> 10 01-2007 150
> 11 01-2007 300
> 12 01-2007 575
> 14 01-2007 25
> 21 01-2007 50000
> 22 01-2007 25000
> 31 01-2007 8700
> 51 01-2007 330
>
> I need to combine the ones with 10, 11 & 12 into one line, number 14 on
> one line, 21 & 22 into one line, and 31 & 51 on a single line each
> respectively.
>
> The groupby is currently on the first 2 characters of the varchar field,
> which gives me 3 separate entries for 10 11 & 12, 2 separate entries on
> 21 & 22, etc.
>
> Group1 01-2007 150
> Group1 01-2007 300
> Group1 01-2007 575
> Group2 01-2007 25
> Group3 01-2007 50000
> Group3 01-2007 25000
> Group4 01-2007 8700
> Group5 01-2007 330
>
> I have to evaluate the first two characters of that field to determine
> which group it's supposed to be in.
>
> How do you do a groupby to give you:
>
> Group1 01-2007 1025
> Group2 01-2007 25
> Group3 01-2007 75000
> Group4 01-2007 8700
> Group5 01-2007 330
>
> Thanks,
>
> BC


You need to create a temp table with additional column(s) to give you
the flexiblity to work with the table.

So your new table would be like:

Group Type Value

1 10 01-2007 150
1 11 01-2007 300
1 12 01-2007 575
2 14 01-2007 25
3 21 01-2007 50000
3 22 01-2007 25000
4 31 01-2007 8700
5 51 01-2007 330

Next, you need to extract only the proper char for you type in the
able table.
from there, you can do whatever you like to do with it.

--CELKO--

3/9/2007 5:39:00 AM

0

>> I have a table with the following data in a varchar field [sic] (the "10
01-2007" below) and a number (units). <<

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it. Also, if you are going to post vague narratives, at least put
proper single quote marks on the data.

If you had followed basic Netiquette, would it look like this?

CREATE TABLE Foobar
(vague_name CHAR(10) NOT NULL, -- sample datra looked fixed
some_value INTEGER NOT NULL, -- constraints?
PRIMARY KEY (vague_name, some_value));

INSERT INTO Foobar VALUES ('10 01-2007, 150);
Etc.

You do not understand that a column is not anything like a field, so
you did not bother with DDL that would show us that ***VITAL
INFORMATION ***

I am also hoping that you did not screw up and use strings for
temporal data, but the 01-2007 scares me. If you had specs, I could
read them and see if I am right.

>> I need to combine the ones with 10, 11 & 12 into one line [sic] number 14 on
one line [sic], 21 & 22 into one line [sic], and 31 & 51 on a single
line [sic] each respectively. <<

You do not have any abstract thinking ability, do you? Lines are
physical things on a report and not part of a data model or a schema.
Confusing fields was bad enough, but this is truly awful. Formatting
and physical output is done int he front end and nothing to do with
the database.

Create a table:

CREATE TABLE ReportGroups
(group_name CHAR (8) NOT NULL,
some_prefix CHAR(2) NOT NULL,
PRIMARY KEY(group_name, some_prefix));

Load it with whatever the current rules and do a join

SELECT R.group_name, SUBSTRING (vague_name, 4, 10) , F.some_value
FROM Foobar AS F, ReportGroups AS R
WHERE SUBSTRING (vague_name, 1, 2) = R.some_prefix;

When the groups change, change the table. Avoid hardwiring groups
into code.

Blasting Cap

3/9/2007 4:41:00 PM

0

Tom -

Thanks for the help. How would the "Order By" clause work then? (the
data that I have is not in alphabetical order). The first group begins
with an "F", the second & fourth begin with the letter "A", the third
with an "S" and the last one with a "P".

BC



Tom Moreau wrote:
> Try:
>
> select
> 'Group' +
> case
> when left (Col1, 2) in ('10', '11', '12') then '1'
> when left (Col1, 2) = '14' then '2'
> when left (Col1, 2) in ('21', '22') then '3'
> when left (Col1, 2) = '31' then '4'
> when left (Col1, 2) = '51' then '5'
> end + right (Col1, 7) as TheGroup
> , sum (Col2) as Value
> from
> MyTable
> group by
> 'Group' +
> case
> when left (Col1, 2) in ('10', '11', '12') then '1'
> when left (Col1, 2) = '14' then '2'
> when left (Col1, 2) in ('21', '22') then '3'
> when left (Col1, 2) = '31' then '4'
> when left (Col1, 2) = '51' then '5'
> end + right (Col1, 7)
>

Tom Moreau

3/9/2007 4:52:00 PM

0

The data you provided were numeric, so you'd have to post the actual data
you are using for me to figure out what you're doing. That said, you can
use the CASE expression to roll your own sort order:

ORDER BY
CASE Col1
WHEN 'F' then 1
WHEN 'A' then 2
WHEN 'S' then 3
WHEN 'P' then 4
END

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Blasting Cap" <goober@christian.net> wrote in message
news:erduJnmYHHA.1240@TK2MSFTNGP04.phx.gbl...
Tom -

Thanks for the help. How would the "Order By" clause work then? (the
data that I have is not in alphabetical order). The first group begins
with an "F", the second & fourth begin with the letter "A", the third
with an "S" and the last one with a "P".

BC



Tom Moreau wrote:
> Try:
>
> select
> 'Group' +
> case
> when left (Col1, 2) in ('10', '11', '12') then '1'
> when left (Col1, 2) = '14' then '2'
> when left (Col1, 2) in ('21', '22') then '3'
> when left (Col1, 2) = '31' then '4'
> when left (Col1, 2) = '51' then '5'
> end + right (Col1, 7) as TheGroup
> , sum (Col2) as Value
> from
> MyTable
> group by
> 'Group' +
> case
> when left (Col1, 2) in ('10', '11', '12') then '1'
> when left (Col1, 2) = '14' then '2'
> when left (Col1, 2) in ('21', '22') then '3'
> when left (Col1, 2) = '31' then '4'
> when left (Col1, 2) = '51' then '5'
> end + right (Col1, 7)
>

Blasting Cap

3/9/2007 8:39:00 PM

0

Specs? Who gets specs? It's mostly handed to me and said give me this.

I did not feel that ddl was needed, since the question I had asked was
in pseudo code, as the source(s) for the data still had not been
identified. I was only given a sheet of paper & told to produce it from
a number of tables.


Thank you for your suggestions.

However, I do have one bone to pick with you.

<<You do not have any abstract thinking ability, do you? >>

I did not like that comment from you. No one likes to be called dumb,
ignorant or stupid, even if it may be true. So, if you have to resort
to belittling me to make yourself feel superior, then I've done my good
deed for the day.


--CELKO-- wrote:
>>> I have a table with the following data in a varchar field [sic] (the "10
> 01-2007" below) and a number (units). <<
>
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are. Sample data is also a good idea, along with clear
> specifications. It is very hard to debug code when you do not let us
> see it. Also, if you are going to post vague narratives, at least put
> proper single quote marks on the data.
>
> If you had followed basic Netiquette, would it look like this?
>
> CREATE TABLE Foobar
> (vague_name CHAR(10) NOT NULL, -- sample datra looked fixed
> some_value INTEGER NOT NULL, -- constraints?
> PRIMARY KEY (vague_name, some_value));
>
> INSERT INTO Foobar VALUES ('10 01-2007, 150);
> Etc.
>
> You do not understand that a column is not anything like a field, so
> you did not bother with DDL that would show us that ***VITAL
> INFORMATION ***
>
> I am also hoping that you did not screw up and use strings for
> temporal data, but the 01-2007 scares me. If you had specs, I could
> read them and see if I am right.
>
>>> I need to combine the ones with 10, 11 & 12 into one line [sic] number 14 on
> one line [sic], 21 & 22 into one line [sic], and 31 & 51 on a single
> line [sic] each respectively. <<
>
> You do not have any abstract thinking ability, do you? Lines are
> physical things on a report and not part of a data model or a schema.
> Confusing fields was bad enough, but this is truly awful. Formatting
> and physical output is done int he front end and nothing to do with
> the database.
>
> Create a table:
>
> CREATE TABLE ReportGroups
> (group_name CHAR (8) NOT NULL,
> some_prefix CHAR(2) NOT NULL,
> PRIMARY KEY(group_name, some_prefix));
>
> Load it with whatever the current rules and do a join
>
> SELECT R.group_name, SUBSTRING (vague_name, 4, 10) , F.some_value
> FROM Foobar AS F, ReportGroups AS R
> WHERE SUBSTRING (vague_name, 1, 2) = R.some_prefix;
>
> When the groups change, change the table. Avoid hardwiring groups
> into code.
>

Blasting Cap

3/12/2007 3:15:00 PM

0

Tom:

After we finally decided on the sources for the data, they created a
table that actually has data in it we're wanting to use, instead of just
thinking of how I was anticipating the data to look.

Here's the ddl:


if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[monthly_ships]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[monthly_ships]
GO

CREATE TABLE [dbo].[monthly_ships] (
[BA] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[org] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WHA] [int] NULL ,
[WHA_ar] [int] NULL ,
[DeptStores] [int] NULL ,
[DeptStores_ar] [int] NULL ,
[Asia] [int] NULL ,
[Asia_ar] [int] NULL ,
[Dealers] [int] NULL ,
[Dealers_ar] [int] NULL ,
[GovLaw] [int] NULL ,
[GovLaw_ar] [int] NULL ,
[Other] [int] NULL ,
[Other_ar] [int] NULL ,
[SouthAmerica] [int] NULL ,
[SouthAmerica_ar] [int] NULL
) ON [PRIMARY]
GO



INSERT INTO [monthly_ships]
([BA],[org],[WHA],[WHA_ar],[DeptStores],[DeptStores_ar],[Asia],[Asia_ar],[Dealers],[Dealers_ar],[GovLaw],[GovLaw_ar],[Other],[Other_ar],[SouthAmerica],[SouthAmerica_ar])VALUES('10
01-2007','ORG1',213214,213370,32554,33778,0,0,4411,5996,0,0,25138,2281,0,0)
INSERT INTO [monthly_ships]
([BA],[org],[WHA],[WHA_ar],[DeptStores],[DeptStores_ar],[Asia],[Asia_ar],[Dealers],[Dealers_ar],[GovLaw],[GovLaw_ar],[Other],[Other_ar],[SouthAmerica],[SouthAmerica_ar])VALUES('11
01-2007','ORG1',2298452,2292340,1427145,1426464,0,0,123295,117531,223055,220192,450086,241724,0,0)
INSERT INTO [monthly_ships]
([BA],[org],[WHA],[WHA_ar],[DeptStores],[DeptStores_ar],[Asia],[Asia_ar],[Dealers],[Dealers_ar],[GovLaw],[GovLaw_ar],[Other],[Other_ar],[SouthAmerica],[SouthAmerica_ar])VALUES('12
01-2007','ORG1',0,-7087,0,0,0,0,0,-1584,0,-345,506007,386284,0,0)
INSERT INTO [monthly_ships]
([BA],[org],[WHA],[WHA_ar],[DeptStores],[DeptStores_ar],[Asia],[Asia_ar],[Dealers],[Dealers_ar],[GovLaw],[GovLaw_ar],[Other],[Other_ar],[SouthAmerica],[SouthAmerica_ar])VALUES('14
01-2007','ORG1',274401,274401,323137,323137,0,0,4049,4781,0,0,2323,3894,0,0)
INSERT INTO [monthly_ships]
([BA],[org],[WHA],[WHA_ar],[DeptStores],[DeptStores_ar],[Asia],[Asia_ar],[Dealers],[Dealers_ar],[GovLaw],[GovLaw_ar],[Other],[Other_ar],[SouthAmerica],[SouthAmerica_ar])VALUES('21
01-2007','ORG1',1633450,1635968,3656214,3660268,0,0,5560,5562,306386,282301,989948,848059,0,0)
INSERT INTO [monthly_ships]
([BA],[org],[WHA],[WHA_ar],[DeptStores],[DeptStores_ar],[Asia],[Asia_ar],[Dealers],[Dealers_ar],[GovLaw],[GovLaw_ar],[Other],[Other_ar],[SouthAmerica],[SouthAmerica_ar])VALUES('22
01-2007','ORG1',0,0,48660,48660,0,0,0,0,0,0,0,0,0,0)
INSERT INTO [monthly_ships]
([BA],[org],[WHA],[WHA_ar],[DeptStores],[DeptStores_ar],[Asia],[Asia_ar],[Dealers],[Dealers_ar],[GovLaw],[GovLaw_ar],[Other],[Other_ar],[SouthAmerica],[SouthAmerica_ar])VALUES('31
01-2007','ORG1',109216,108809,149904,152029,0,0,2870,2876,2217,1213,32089,5463,0,0)
INSERT INTO [monthly_ships]
([BA],[org],[WHA],[WHA_ar],[DeptStores],[DeptStores_ar],[Asia],[Asia_ar],[Dealers],[Dealers_ar],[GovLaw],[GovLaw_ar],[Other],[Other_ar],[SouthAmerica],[SouthAmerica_ar])VALUES('33
01-2007','ORG1',0,0,0,0,0,0,0,0,55033,51751,0,0,0,0)
INSERT INTO [monthly_ships]
([BA],[org],[WHA],[WHA_ar],[DeptStores],[DeptStores_ar],[Asia],[Asia_ar],[Dealers],[Dealers_ar],[GovLaw],[GovLaw_ar],[Other],[Other_ar],[SouthAmerica],[SouthAmerica_ar])VALUES('51
01-2007','ORG1',0,0,0,0,0,0,0,0,0,0,231111,231282,0,0)
INSERT INTO [monthly_ships]
([BA],[org],[WHA],[WHA_ar],[DeptStores],[DeptStores_ar],[Asia],[Asia_ar],[Dealers],[Dealers_ar],[GovLaw],[GovLaw_ar],[Other],[Other_ar],[SouthAmerica],[SouthAmerica_ar])VALUES('10
01-2007','ORG2',0,0,0,0,118307,62220,0,0,0,0,0,0,0,0)
INSERT INTO [monthly_ships]
([BA],[org],[WHA],[WHA_ar],[DeptStores],[DeptStores_ar],[Asia],[Asia_ar],[Dealers],[Dealers_ar],[GovLaw],[GovLaw_ar],[Other],[Other_ar],[SouthAmerica],[SouthAmerica_ar])VALUES('11
01-2007','ORG2',0,0,0,0,338472,96918,0,0,0,0,0,0,0,0)
INSERT INTO [monthly_ships]
([BA],[org],[WHA],[WHA_ar],[DeptStores],[DeptStores_ar],[Asia],[Asia_ar],[Dealers],[Dealers_ar],[GovLaw],[GovLaw_ar],[Other],[Other_ar],[SouthAmerica],[SouthAmerica_ar])VALUES('12
01-2007','ORG2',0,0,0,0,199,43,0,0,0,0,0,0,0,0)
INSERT INTO [monthly_ships]
([BA],[org],[WHA],[WHA_ar],[DeptStores],[DeptStores_ar],[Asia],[Asia_ar],[Dealers],[Dealers_ar],[GovLaw],[GovLaw_ar],[Other],[Other_ar],[SouthAmerica],[SouthAmerica_ar])VALUES('14
01-2007','ORG2',0,0,0,0,22532,10622,0,0,0,0,0,0,0,0)
INSERT INTO [monthly_ships]
([BA],[org],[WHA],[WHA_ar],[DeptStores],[DeptStores_ar],[Asia],[Asia_ar],[Dealers],[Dealers_ar],[GovLaw],[GovLaw_ar],[Other],[Other_ar],[SouthAmerica],[SouthAmerica_ar])VALUES('21
01-2007','ORG2',0,0,0,0,1320419,1320852,0,0,0,0,0,0,0,0)
INSERT INTO [monthly_ships]
([BA],[org],[WHA],[WHA_ar],[DeptStores],[DeptStores_ar],[Asia],[Asia_ar],[Dealers],[Dealers_ar],[GovLaw],[GovLaw_ar],[Other],[Other_ar],[SouthAmerica],[SouthAmerica_ar])VALUES('22
01-2007','ORG2',0,0,0,0,48476,48476,0,0,0,0,0,0,0,0)
INSERT INTO [monthly_ships]
([BA],[org],[WHA],[WHA_ar],[DeptStores],[DeptStores_ar],[Asia],[Asia_ar],[Dealers],[Dealers_ar],[GovLaw],[GovLaw_ar],[Other],[Other_ar],[SouthAmerica],[SouthAmerica_ar])VALUES('31
01-2007','ORG2',0,0,0,0,66082,66158,0,0,0,0,0,0,0,0)
INSERT INTO [monthly_ships]
([BA],[org],[WHA],[WHA_ar],[DeptStores],[DeptStores_ar],[Asia],[Asia_ar],[Dealers],[Dealers_ar],[GovLaw],[GovLaw_ar],[Other],[Other_ar],[SouthAmerica],[SouthAmerica_ar])VALUES('10
01-2007','ORG3',0,0,0,0,0,0,0,0,0,0,0,0,51000,50184)
INSERT INTO [monthly_ships]
([BA],[org],[WHA],[WHA_ar],[DeptStores],[DeptStores_ar],[Asia],[Asia_ar],[Dealers],[Dealers_ar],[GovLaw],[GovLaw_ar],[Other],[Other_ar],[SouthAmerica],[SouthAmerica_ar])VALUES('11
01-2007','ORG3',0,0,0,0,0,0,0,0,0,0,0,0,1035136,952032)
INSERT INTO [monthly_ships]
([BA],[org],[WHA],[WHA_ar],[DeptStores],[DeptStores_ar],[Asia],[Asia_ar],[Dealers],[Dealers_ar],[GovLaw],[GovLaw_ar],[Other],[Other_ar],[SouthAmerica],[SouthAmerica_ar])VALUES('12
01-2007','ORG3',0,0,0,0,0,0,0,0,0,0,0,0,12274,10200)
INSERT INTO [monthly_ships]
([BA],[org],[WHA],[WHA_ar],[DeptStores],[DeptStores_ar],[Asia],[Asia_ar],[Dealers],[Dealers_ar],[GovLaw],[GovLaw_ar],[Other],[Other_ar],[SouthAmerica],[SouthAmerica_ar])VALUES('14
01-2007','ORG3',0,0,0,0,0,0,0,0,0,0,0,0,32922,29278)
INSERT INTO [monthly_ships]
([BA],[org],[WHA],[WHA_ar],[DeptStores],[DeptStores_ar],[Asia],[Asia_ar],[Dealers],[Dealers_ar],[GovLaw],[GovLaw_ar],[Other],[Other_ar],[SouthAmerica],[SouthAmerica_ar])VALUES('21
01-2007','ORG3',0,0,0,0,0,0,0,0,0,0,0,0,858612,715973)
INSERT INTO [monthly_ships]
([BA],[org],[WHA],[WHA_ar],[DeptStores],[DeptStores_ar],[Asia],[Asia_ar],[Dealers],[Dealers_ar],[GovLaw],[GovLaw_ar],[Other],[Other_ar],[SouthAmerica],[SouthAmerica_ar])VALUES('31
01-2007','ORG3',0,0,0,0,0,0,0,0,0,0,0,0,69763,68817)



Here's the query:


select
x =
case
when left(ba,2) in ('10','11','12') then
'FireExtenguishers '
when left(ba,2) in ('13','14') then
'Sealed FirePrevention '
when left(ba,2) in ('21','22') then
'Automated '
when left(ba,2) in ('31') then
'Accessories '
else
'Other '
end+ substring(ba,4,10),
sum(WHA) as 'WHA',
sum(WHA_ar) as 'WHA_ar',
sum(DeptStores) as 'DeptStores',
sum(DeptStores_ar) as 'DeptStores_ar',
sum(Asia) as 'Asia',
sum(Asia_ar) as 'Asia_ar',
sum(Dealers) as 'Dealers',
sum(Dealers_ar) as 'Dealers_ar',
sum(GovLaw) as 'GovLaw',
sum(GovLaw_ar) as 'GovLaw_ar',
sum(Other) as 'Other',
sum(Other_ar) as 'Other_ar',
sum(SouthAmerica) as 'SouthAmerica',
sum(SouthAmerica_ar) as 'SouthAmerica_ar'
from monthly_ships
group by
case
when left(ba,2) in ('10','11','12') then
'FireExtenguishers '
when left(ba,2) in ('13','14') then
'Sealed FirePrevention '
when left(ba,2) in ('21','22') then
'Automated '
when left(ba,2) in ('31') then
'Accessories '
else
'Other '
end+ substring(ba,4,10)


How it comes out is:

Accessories 01-2007
Automated 01-2007
FireExtenguishers 01-2007
Other 01-2007
Sealed FirePrevention 01-2007

How I want it to be listed is:

FireExtenguishers 01-2007
Sealed FirePrevention 01-2007
Automated 01-2007
Accessories 01-2007
Other 01-2007

In addition to that, on the columns with the numbers in them - WHA,
WHA_ar, DeptStores, Deptstores_ar and so on - I want to provide a line
with a total on it. I know that'll be done with rollup on the grouping,
but I am not sure of the syntax yet.










Tom Moreau wrote:
> The data you provided were numeric, so you'd have to post the actual data
> you are using for me to figure out what you're doing. That said, you can
> use the CASE expression to roll your own sort order:
>
> ORDER BY
> CASE Col1
> WHEN 'F' then 1
> WHEN 'A' then 2
> WHEN 'S' then 3
> WHEN 'P' then 4
> END
>

Tom Moreau

3/12/2007 3:30:00 PM

0

Thanx for the DDL, the data, the spec and your attempted query. It made it
easy to produce a solution for you:

select
*
from
(
select
x =
case
when left(ba,2) in ('10','11','12') then
'FireExtenguishers '
when left(ba,2) in ('13','14') then
'Sealed FirePrevention '
when left(ba,2) in ('21','22') then
'Automated '
when left(ba,2) in ('31') then
'Accessories '
else
'Other '
end+ substring(ba,4,10),
sum(WHA) as 'WHA',
sum(WHA_ar) as 'WHA_ar',
sum(DeptStores) as 'DeptStores',
sum(DeptStores_ar) as 'DeptStores_ar',
sum(Asia) as 'Asia',
sum(Asia_ar) as 'Asia_ar',
sum(Dealers) as 'Dealers',
sum(Dealers_ar) as 'Dealers_ar',
sum(GovLaw) as 'GovLaw',
sum(GovLaw_ar) as 'GovLaw_ar',
sum(Other) as 'Other',
sum(Other_ar) as 'Other_ar',
sum(SouthAmerica) as 'SouthAmerica',
sum(SouthAmerica_ar) as 'SouthAmerica_ar'
from monthly_ships
group by
case
when left(ba,2) in ('10','11','12') then
'FireExtenguishers '
when left(ba,2) in ('13','14') then
'Sealed FirePrevention '
when left(ba,2) in ('21','22') then
'Automated '
when left(ba,2) in ('31') then
'Accessories '
else
'Other '
end+ substring(ba,4,10)
with rollup
) as z
order by
case x
when 'FireExtenguishers 01-2007' then 1
when 'Sealed FirePrevention 01-2007' then 2
when 'Automated 01-2007' then 3
when 'Accessories 01-2007' then 4
when 'Other 01-2007' then 5
else 6
end


--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Blasting Cap" <goober@christian.net> wrote in message
news:OLO0AlLZHHA.688@TK2MSFTNGP03.phx.gbl...
Tom:

After we finally decided on the sources for the data, they created a
table that actually has data in it we're wanting to use, instead of just
thinking of how I was anticipating the data to look.

Here's the ddl:


if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[monthly_ships]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[monthly_ships]
GO

CREATE TABLE [dbo].[monthly_ships] (
[BA] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[org] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WHA] [int] NULL ,
[WHA_ar] [int] NULL ,
[DeptStores] [int] NULL ,
[DeptStores_ar] [int] NULL ,
[Asia] [int] NULL ,
[Asia_ar] [int] NULL ,
[Dealers] [int] NULL ,
[Dealers_ar] [int] NULL ,
[GovLaw] [int] NULL ,
[GovLaw_ar] [int] NULL ,
[Other] [int] NULL ,
[Other_ar] [int] NULL ,
[SouthAmerica] [int] NULL ,
[SouthAmerica_ar] [int] NULL
) ON [PRIMARY]
GO



INSERT INTO [monthly_ships]
([BA],[org],[WHA],[WHA_ar],[DeptStores],[DeptStores_ar],[Asia],[Asia_ar],[Dealers],[Dealers_ar],[GovLaw],[GovLaw_ar],[Other],[Other_ar],[SouthAmerica],[SouthAmerica_ar])VALUES('10
01-2007','ORG1',213214,213370,32554,33778,0,0,4411,5996,0,0,25138,2281,0,0)
INSERT INTO [monthly_ships]
([BA],[org],[WHA],[WHA_ar],[DeptStores],[DeptStores_ar],[Asia],[Asia_ar],[Dealers],[Dealers_ar],[GovLaw],[GovLaw_ar],[Other],[Other_ar],[SouthAmerica],[SouthAmerica_ar])VALUES('11
01-2007','ORG1',2298452,2292340,1427145,1426464,0,0,123295,117531,223055,220192,450086,241724,0,0)
INSERT INTO [monthly_ships]
([BA],[org],[WHA],[WHA_ar],[DeptStores],[DeptStores_ar],[Asia],[Asia_ar],[Dealers],[Dealers_ar],[GovLaw],[GovLaw_ar],[Other],[Other_ar],[SouthAmerica],[SouthAmerica_ar])VALUES('12
01-2007','ORG1',0,-7087,0,0,0,0,0,-1584,0,-345,506007,386284,0,0)
INSERT INTO [monthly_ships]
([BA],[org],[WHA],[WHA_ar],[DeptStores],[DeptStores_ar],[Asia],[Asia_ar],[Dealers],[Dealers_ar],[GovLaw],[GovLaw_ar],[Other],[Other_ar],[SouthAmerica],[SouthAmerica_ar])VALUES('14
01-2007','ORG1',274401,274401,323137,323137,0,0,4049,4781,0,0,2323,3894,0,0)
INSERT INTO [monthly_ships]
([BA],[org],[WHA],[WHA_ar],[DeptStores],[DeptStores_ar],[Asia],[Asia_ar],[Dealers],[Dealers_ar],[GovLaw],[GovLaw_ar],[Other],[Other_ar],[SouthAmerica],[SouthAmerica_ar])VALUES('21
01-2007','ORG1',1633450,1635968,3656214,3660268,0,0,5560,5562,306386,282301,989948,848059,0,0)
INSERT INTO [monthly_ships]
([BA],[org],[WHA],[WHA_ar],[DeptStores],[DeptStores_ar],[Asia],[Asia_ar],[Dealers],[Dealers_ar],[GovLaw],[GovLaw_ar],[Other],[Other_ar],[SouthAmerica],[SouthAmerica_ar])VALUES('22
01-2007','ORG1',0,0,48660,48660,0,0,0,0,0,0,0,0,0,0)
INSERT INTO [monthly_ships]
([BA],[org],[WHA],[WHA_ar],[DeptStores],[DeptStores_ar],[Asia],[Asia_ar],[Dealers],[Dealers_ar],[GovLaw],[GovLaw_ar],[Other],[Other_ar],[SouthAmerica],[SouthAmerica_ar])VALUES('31
01-2007','ORG1',109216,108809,149904,152029,0,0,2870,2876,2217,1213,32089,5463,0,0)
INSERT INTO [monthly_ships]
([BA],[org],[WHA],[WHA_ar],[DeptStores],[DeptStores_ar],[Asia],[Asia_ar],[Dealers],[Dealers_ar],[GovLaw],[GovLaw_ar],[Other],[Other_ar],[SouthAmerica],[SouthAmerica_ar])VALUES('33
01-2007','ORG1',0,0,0,0,0,0,0,0,55033,51751,0,0,0,0)
INSERT INTO [monthly_ships]
([BA],[org],[WHA],[WHA_ar],[DeptStores],[DeptStores_ar],[Asia],[Asia_ar],[Dealers],[Dealers_ar],[GovLaw],[GovLaw_ar],[Other],[Other_ar],[SouthAmerica],[SouthAmerica_ar])VALUES('51
01-2007','ORG1',0,0,0,0,0,0,0,0,0,0,231111,231282,0,0)
INSERT INTO [monthly_ships]
([BA],[org],[WHA],[WHA_ar],[DeptStores],[DeptStores_ar],[Asia],[Asia_ar],[Dealers],[Dealers_ar],[GovLaw],[GovLaw_ar],[Other],[Other_ar],[SouthAmerica],[SouthAmerica_ar])VALUES('10
01-2007','ORG2',0,0,0,0,118307,62220,0,0,0,0,0,0,0,0)
INSERT INTO [monthly_ships]
([BA],[org],[WHA],[WHA_ar],[DeptStores],[DeptStores_ar],[Asia],[Asia_ar],[Dealers],[Dealers_ar],[GovLaw],[GovLaw_ar],[Other],[Other_ar],[SouthAmerica],[SouthAmerica_ar])VALUES('11
01-2007','ORG2',0,0,0,0,338472,96918,0,0,0,0,0,0,0,0)
INSERT INTO [monthly_ships]
([BA],[org],[WHA],[WHA_ar],[DeptStores],[DeptStores_ar],[Asia],[Asia_ar],[Dealers],[Dealers_ar],[GovLaw],[GovLaw_ar],[Other],[Other_ar],[SouthAmerica],[SouthAmerica_ar])VALUES('12
01-2007','ORG2',0,0,0,0,199,43,0,0,0,0,0,0,0,0)
INSERT INTO [monthly_ships]
([BA],[org],[WHA],[WHA_ar],[DeptStores],[DeptStores_ar],[Asia],[Asia_ar],[Dealers],[Dealers_ar],[GovLaw],[GovLaw_ar],[Other],[Other_ar],[SouthAmerica],[SouthAmerica_ar])VALUES('14
01-2007','ORG2',0,0,0,0,22532,10622,0,0,0,0,0,0,0,0)
INSERT INTO [monthly_ships]
([BA],[org],[WHA],[WHA_ar],[DeptStores],[DeptStores_ar],[Asia],[Asia_ar],[Dealers],[Dealers_ar],[GovLaw],[GovLaw_ar],[Other],[Other_ar],[SouthAmerica],[SouthAmerica_ar])VALUES('21
01-2007','ORG2',0,0,0,0,1320419,1320852,0,0,0,0,0,0,0,0)
INSERT INTO [monthly_ships]
([BA],[org],[WHA],[WHA_ar],[DeptStores],[DeptStores_ar],[Asia],[Asia_ar],[Dealers],[Dealers_ar],[GovLaw],[GovLaw_ar],[Other],[Other_ar],[SouthAmerica],[SouthAmerica_ar])VALUES('22
01-2007','ORG2',0,0,0,0,48476,48476,0,0,0,0,0,0,0,0)
INSERT INTO [monthly_ships]
([BA],[org],[WHA],[WHA_ar],[DeptStores],[DeptStores_ar],[Asia],[Asia_ar],[Dealers],[Dealers_ar],[GovLaw],[GovLaw_ar],[Other],[Other_ar],[SouthAmerica],[SouthAmerica_ar])VALUES('31
01-2007','ORG2',0,0,0,0,66082,66158,0,0,0,0,0,0,0,0)
INSERT INTO [monthly_ships]
([BA],[org],[WHA],[WHA_ar],[DeptStores],[DeptStores_ar],[Asia],[Asia_ar],[Dealers],[Dealers_ar],[GovLaw],[GovLaw_ar],[Other],[Other_ar],[SouthAmerica],[SouthAmerica_ar])VALUES('10
01-2007','ORG3',0,0,0,0,0,0,0,0,0,0,0,0,51000,50184)
INSERT INTO [monthly_ships]
([BA],[org],[WHA],[WHA_ar],[DeptStores],[DeptStores_ar],[Asia],[Asia_ar],[Dealers],[Dealers_ar],[GovLaw],[GovLaw_ar],[Other],[Other_ar],[SouthAmerica],[SouthAmerica_ar])VALUES('11
01-2007','ORG3',0,0,0,0,0,0,0,0,0,0,0,0,1035136,952032)
INSERT INTO [monthly_ships]
([BA],[org],[WHA],[WHA_ar],[DeptStores],[DeptStores_ar],[Asia],[Asia_ar],[Dealers],[Dealers_ar],[GovLaw],[GovLaw_ar],[Other],[Other_ar],[SouthAmerica],[SouthAmerica_ar])VALUES('12
01-2007','ORG3',0,0,0,0,0,0,0,0,0,0,0,0,12274,10200)
INSERT INTO [monthly_ships]
([BA],[org],[WHA],[WHA_ar],[DeptStores],[DeptStores_ar],[Asia],[Asia_ar],[Dealers],[Dealers_ar],[GovLaw],[GovLaw_ar],[Other],[Other_ar],[SouthAmerica],[SouthAmerica_ar])VALUES('14
01-2007','ORG3',0,0,0,0,0,0,0,0,0,0,0,0,32922,29278)
INSERT INTO [monthly_ships]
([BA],[org],[WHA],[WHA_ar],[DeptStores],[DeptStores_ar],[Asia],[Asia_ar],[Dealers],[Dealers_ar],[GovLaw],[GovLaw_ar],[Other],[Other_ar],[SouthAmerica],[SouthAmerica_ar])VALUES('21
01-2007','ORG3',0,0,0,0,0,0,0,0,0,0,0,0,858612,715973)
INSERT INTO [monthly_ships]
([BA],[org],[WHA],[WHA_ar],[DeptStores],[DeptStores_ar],[Asia],[Asia_ar],[Dealers],[Dealers_ar],[GovLaw],[GovLaw_ar],[Other],[Other_ar],[SouthAmerica],[SouthAmerica_ar])VALUES('31
01-2007','ORG3',0,0,0,0,0,0,0,0,0,0,0,0,69763,68817)



Here's the query:


select
x =
case
when left(ba,2) in ('10','11','12') then
'FireExtenguishers '
when left(ba,2) in ('13','14') then
'Sealed FirePrevention '
when left(ba,2) in ('21','22') then
'Automated '
when left(ba,2) in ('31') then
'Accessories '
else
'Other '
end+ substring(ba,4,10),
sum(WHA) as 'WHA',
sum(WHA_ar) as 'WHA_ar',
sum(DeptStores) as 'DeptStores',
sum(DeptStores_ar) as 'DeptStores_ar',
sum(Asia) as 'Asia',
sum(Asia_ar) as 'Asia_ar',
sum(Dealers) as 'Dealers',
sum(Dealers_ar) as 'Dealers_ar',
sum(GovLaw) as 'GovLaw',
sum(GovLaw_ar) as 'GovLaw_ar',
sum(Other) as 'Other',
sum(Other_ar) as 'Other_ar',
sum(SouthAmerica) as 'SouthAmerica',
sum(SouthAmerica_ar) as 'SouthAmerica_ar'
from monthly_ships
group by
case
when left(ba,2) in ('10','11','12') then
'FireExtenguishers '
when left(ba,2) in ('13','14') then
'Sealed FirePrevention '
when left(ba,2) in ('21','22') then
'Automated '
when left(ba,2) in ('31') then
'Accessories '
else
'Other '
end+ substring(ba,4,10)


How it comes out is:

Accessories 01-2007
Automated 01-2007
FireExtenguishers 01-2007
Other 01-2007
Sealed FirePrevention 01-2007

How I want it to be listed is:

FireExtenguishers 01-2007
Sealed FirePrevention 01-2007
Automated 01-2007
Accessories 01-2007
Other 01-2007

In addition to that, on the columns with the numbers in them - WHA,
WHA_ar, DeptStores, Deptstores_ar and so on - I want to provide a line
with a total on it. I know that'll be done with rollup on the grouping,
but I am not sure of the syntax yet.










Tom Moreau wrote:
> The data you provided were numeric, so you'd have to post the actual data
> you are using for me to figure out what you're doing. That said, you can
> use the CASE expression to roll your own sort order:
>
> ORDER BY
> CASE Col1
> WHEN 'F' then 1
> WHEN 'A' then 2
> WHEN 'S' then 3
> WHEN 'P' then 4
> END
>

Blasting Cap

3/12/2007 4:25:00 PM

0

Tom - thanks for the help.

I see what I was trying to do - to do both the order AND the select &
groupby in 1 step.... sort of the "you can't get there from here" type
of thing.

What I would like to learn is how to know "when" to do what your
suggestion was - as far as the order by being separate from the group by.

Can you recommend any good reference books or courses?

Thank you again

BC


Tom Moreau wrote:
> Thanx for the DDL, the data, the spec and your attempted query. It made it
> easy to produce a solution for you:
>
> select
> *
> from
> (
> select
> x =
> case
> when left(ba,2) in ('10','11','12') then
> 'FireExtenguishers '
> when left(ba,2) in ('13','14') then
> 'Sealed FirePrevention '
> when left(ba,2) in ('21','22') then
> 'Automated '
> when left(ba,2) in ('31') then
> 'Accessories '
> else
> 'Other '
> end+ substring(ba,4,10),
> sum(WHA) as 'WHA',
> sum(WHA_ar) as 'WHA_ar',
> sum(DeptStores) as 'DeptStores',
> sum(DeptStores_ar) as 'DeptStores_ar',
> sum(Asia) as 'Asia',
> sum(Asia_ar) as 'Asia_ar',
> sum(Dealers) as 'Dealers',
> sum(Dealers_ar) as 'Dealers_ar',
> sum(GovLaw) as 'GovLaw',
> sum(GovLaw_ar) as 'GovLaw_ar',
> sum(Other) as 'Other',
> sum(Other_ar) as 'Other_ar',
> sum(SouthAmerica) as 'SouthAmerica',
> sum(SouthAmerica_ar) as 'SouthAmerica_ar'
> from monthly_ships
> group by
> case
> when left(ba,2) in ('10','11','12') then
> 'FireExtenguishers '
> when left(ba,2) in ('13','14') then
> 'Sealed FirePrevention '
> when left(ba,2) in ('21','22') then
> 'Automated '
> when left(ba,2) in ('31') then
> 'Accessories '
> else
> 'Other '
> end+ substring(ba,4,10)
> with rollup
> ) as z
> order by
> case x
> when 'FireExtenguishers 01-2007' then 1
> when 'Sealed FirePrevention 01-2007' then 2
> when 'Automated 01-2007' then 3
> when 'Accessories 01-2007' then 4
> when 'Other 01-2007' then 5
> else 6
> end
>
>

Tom Moreau

3/12/2007 5:58:00 PM

0

If you Google "derived tables", that should net you a few good hits on the
ruse I used. In the meantime, I did write about rolling your own sort order
in "Advanced Transact-SQL for SQL Server 2000". For SQL Server 2005, check
out www.insidetsqlserver.com. As for course, check out
www.solidqualitylearning.com.

Keep in mind that, just be cause you do a GROUP on something, it doesn't
mean that you need/want to ORDER the same columns. For example, you may
want to ORDER BY the aggregate, such as who had the most sales, grouped by
employee ID.

HTH

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Blasting Cap" <goober@christian.net> wrote in message
news:%23%23Al3LMZHHA.1240@TK2MSFTNGP04.phx.gbl...
Tom - thanks for the help.

I see what I was trying to do - to do both the order AND the select &
groupby in 1 step.... sort of the "you can't get there from here" type
of thing.

What I would like to learn is how to know "when" to do what your
suggestion was - as far as the order by being separate from the group by.

Can you recommend any good reference books or courses?

Thank you again

BC


Tom Moreau wrote:
> Thanx for the DDL, the data, the spec and your attempted query. It made
> it
> easy to produce a solution for you:
>
> select
> *
> from
> (
> select
> x =
> case
> when left(ba,2) in ('10','11','12') then
> 'FireExtenguishers '
> when left(ba,2) in ('13','14') then
> 'Sealed FirePrevention '
> when left(ba,2) in ('21','22') then
> 'Automated '
> when left(ba,2) in ('31') then
> 'Accessories '
> else
> 'Other '
> end+ substring(ba,4,10),
> sum(WHA) as 'WHA',
> sum(WHA_ar) as 'WHA_ar',
> sum(DeptStores) as 'DeptStores',
> sum(DeptStores_ar) as 'DeptStores_ar',
> sum(Asia) as 'Asia',
> sum(Asia_ar) as 'Asia_ar',
> sum(Dealers) as 'Dealers',
> sum(Dealers_ar) as 'Dealers_ar',
> sum(GovLaw) as 'GovLaw',
> sum(GovLaw_ar) as 'GovLaw_ar',
> sum(Other) as 'Other',
> sum(Other_ar) as 'Other_ar',
> sum(SouthAmerica) as 'SouthAmerica',
> sum(SouthAmerica_ar) as 'SouthAmerica_ar'
> from monthly_ships
> group by
> case
> when left(ba,2) in ('10','11','12') then
> 'FireExtenguishers '
> when left(ba,2) in ('13','14') then
> 'Sealed FirePrevention '
> when left(ba,2) in ('21','22') then
> 'Automated '
> when left(ba,2) in ('31') then
> 'Accessories '
> else
> 'Other '
> end+ substring(ba,4,10)
> with rollup
> ) as z
> order by
> case x
> when 'FireExtenguishers 01-2007' then 1
> when 'Sealed FirePrevention 01-2007' then 2
> when 'Automated 01-2007' then 3
> when 'Accessories 01-2007' then 4
> when 'Other 01-2007' then 5
> else 6
> end
>
>