Count and sub-select - 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)

Count and sub-select

Author

Topic

djavet
Starting Member

36 Posts

Posted - 2004-08-26 : 11:28:52

Hello,

I'm new to SQL.
For a statistic application, I wish know the subtotal of lines pro region (Mitte, ost, west, ost, etc).

How can I do that?

A lot of thx for your help and time,
Regards, Dominique

PS: Is that possible to have a alias into a sub-select? Like something AS subtotal?


SELECT
  distinct case
      when ANZSUCHEN.KANTON = '' then 'nicht_zugeteilt'
      when ANZSUCHEN.KANTON = '----------------------------------' then 'nicht_zugeteilt'
      when ANZSUCHEN.KANTON = 'AG' then 'mitte'
      when ANZSUCHEN.KANTON = 'AI' then 'ost'
      when ANZSUCHEN.KANTON = 'AR' then 'ost'
      when ANZSUCHEN.KANTON = 'BE' then 'bern'
      when ANZSUCHEN.KANTON = 'BL' then 'mitte'
      when ANZSUCHEN.KANTON = 'BS' then 'mitte'
      when ANZSUCHEN.KANTON = 'FR' then 'west'
      when ANZSUCHEN.KANTON = 'GE' then 'west'
      when ANZSUCHEN.KANTON = 'GL' then 'ost'
      when ANZSUCHEN.KANTON = 'GR' then 'ost'
      when ANZSUCHEN.KANTON = 'JU' then 'west'
      when ANZSUCHEN.KANTON = 'LU' then 'mitte'
      when ANZSUCHEN.KANTON = 'NE' then 'west'
      when ANZSUCHEN.KANTON = 'NW' then 'mitte'
      when ANZSUCHEN.KANTON = 'OW' then 'mitte'
      when ANZSUCHEN.KANTON = 'SG' then 'ost'
      when ANZSUCHEN.KANTON = 'SH' then 'ost'
      when ANZSUCHEN.KANTON = 'SO' then 'mitte'
      when ANZSUCHEN.KANTON = 'SZ' then 'mitte'
      when ANZSUCHEN.KANTON = 'TG' then 'ost'
      when ANZSUCHEN.KANTON = 'TI' then 'west'
      when ANZSUCHEN.KANTON = 'UR' then 'mitte'
      when ANZSUCHEN.KANTON = 'VD' then 'west'
      when ANZSUCHEN.KANTON = 'VS' then 'west'
      when ANZSUCHEN.KANTON = 'ZG' then 'mitte'
      when ANZSUCHEN.KANTON = 'ZH' then 'ost'
  end as region,
  (SELECT count(*) FROM ANZSUCHEN WHERE ANZSUCHEN.KANTON = 'FR')
FROM
  ANZSUCHEN
GROUP BY
  ANZSUCHEN.KANTON
ORDER BY
  region

Results:


region   (No colomn name)
bern	34
mitte	34
nicht_zugeteilt	34
ost	34
west	34

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-08-26 : 12:36:28

[code]
select
region, count(*)
from
( select
case
when ANZSUCHEN.KANTON = '' then 'nicht_zugeteilt'
when ANZSUCHEN.KANTON = '----------------------------------' then 'nicht_zugeteilt'
when ANZSUCHEN.KANTON = 'AG' then 'mitte'
when ANZSUCHEN.KANTON = 'AI' then 'ost'
when ANZSUCHEN.KANTON = 'AR' then 'ost'
when ANZSUCHEN.KANTON = 'BE' then 'bern'
when ANZSUCHEN.KANTON = 'BL' then 'mitte'
when ANZSUCHEN.KANTON = 'BS' then 'mitte'
when ANZSUCHEN.KANTON = 'FR' then 'west'
when ANZSUCHEN.KANTON = 'GE' then 'west'
when ANZSUCHEN.KANTON = 'GL' then 'ost'
when ANZSUCHEN.KANTON = 'GR' then 'ost'
when ANZSUCHEN.KANTON = 'JU' then 'west'
when ANZSUCHEN.KANTON = 'LU' then 'mitte'
when ANZSUCHEN.KANTON = 'NE' then 'west'
when ANZSUCHEN.KANTON = 'NW' then 'mitte'
when ANZSUCHEN.KANTON = 'OW' then 'mitte'
when ANZSUCHEN.KANTON = 'SG' then 'ost'
when ANZSUCHEN.KANTON = 'SH' then 'ost'
when ANZSUCHEN.KANTON = 'SO' then 'mitte'
when ANZSUCHEN.KANTON = 'SZ' then 'mitte'
when ANZSUCHEN.KANTON = 'TG' then 'ost'
when ANZSUCHEN.KANTON = 'TI' then 'west'
when ANZSUCHEN.KANTON = 'UR' then 'mitte'
when ANZSUCHEN.KANTON = 'VD' then 'west'
when ANZSUCHEN.KANTON = 'VS' then 'west'
when ANZSUCHEN.KANTON = 'ZG' then 'mitte'
when ANZSUCHEN.KANTON = 'ZH' then 'ost'
end as region
from
ANZSUCHEN ) t
group by
region
[/code]

/rockmoose

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-08-26 : 13:27:23

please store these values in a table and join to it ! or add a column to your table, if that is more appropriate, and just store these values in there.

Never write queries like this in SQL Server!

- Jeff

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-08-26 : 14:47:48

quote:

please store these values in a table and join to it ! or add a column to your table, if that is more appropriate, and just store these values in there.

Never write queries like this in SQL Server!

Point taken, that was really bad sql and maybe suboptimal database design in the first place. Just watch 3NF when you "add a column to your table".

Sometimes it does happen that an "adhoc" grouping is wanted and then some kind of CASE statement will do the trick.
Anyway here is a revised script.


select
  region, count(*)
from
( select
  case
      when ANZSUCHEN.KANTON IN( 'BE' ) then 'bern'
      when ANZSUCHEN.KANTON IN( 'FR', 'GE', 'JU', 'NE', 'TI', 'VD', 'VS' ) then 'west'
      when ANZSUCHEN.KANTON IN( 'AI', 'AR', 'GL', 'GR', 'SG', 'SH', 'TG', 'ZH' ) then 'ost'
      when ANZSUCHEN.KANTON IN( 'AG', 'BL', 'BS', 'LU', 'NW', 'OW', 'SO', 'SZ', 'UR', 'ZG' ) then 'mitte'
      else 'nicht_zugeteilt'
  end as region
from
  ANZSUCHEN ) t
group by
  region

/rockmoose

djavet
Starting Member

36 Posts

Posted - 2004-08-27 : 02:41:06

Thx for your help.
I learn everyday! Sorry for this newbie question, but how can I learn elsewhere? ;o)

May I ask you what is the "t" just after? :
from
ANZSUCHEN ) t

Do you know a good web site where I can learn these way of querize?

Regards, Dominique

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-27 : 05:56:32

this is a great website. i learned a lot here. and so did everybody else.

t is an alias for a derived table (select...)

aliases are used for shortening table names in queries:

select t1.col1, t1.col2, t1.col3, t1.col4, t1.col5
from ThisIsAVeryLongTableNameWhichWouldBeHardToReadInLongQueries t1

for derived tables like that select rockmoose gave you an alias is neccesery so
the sql parser knows how to reference the table.

Go with the flow & have fun! Else fight the flow :)

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-08-27 : 06:48:51

quote:

Do you know a good web site where I can learn these way of querize?

SqlTeam has a section with Books and SQL Resources.
You could browse these to get you further.

Personally
I recommend C.J Date "An introduction to database systems" for fundamentals in database design & theory. ( not how to write Sql Queries though ;-) ).
And of course BOL where I have learnt most of the SqlServer specifics.
Also this forum is a very good place to learn.

/rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources