Count() and sub-select

Dev Shed Forums Sponsor:

Get inside! Sample the range of functionality easily built with JMSL Library for Time Series Data Analysis, Heat Maps, Portfolio Optimization, Monte Carlo Simulation, Stock Price Charting and more. Download Now!

August 26th, 2004, 10:30 AM

djavet

Contributing User

Join Date: Apr 2004

Posts: 31

Time spent in forums: 59 m
Reputation Power: 5

Count() and sub-select

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

Code:

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:

Code:

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

August 26th, 2004, 10:50 AM

swampBoogie

Contributing User

Dev Shed Intermediate (1500 - 1999 posts)

Join Date: Jan 2003

Location: Paris Uppland

Posts: 1,766

Time spent in forums: 2 Weeks 4 Days 6 h 24 m 8 sec
Reputation Power: 37

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) as dt
GROUP BY region
ORDER BY region

August 26th, 2004, 10:53 AM

Blase

Registered User

Join Date: Aug 2004

Posts: 11

Time spent in forums: < 1 sec
Reputation Power: 0

Count()

I would highly recommend a perusal of SQL Books Online for more information on the use of aggregation functions.
Meanwhile, a couple of my observations on your code:
A. Don't use DISTINCT
B. You don't need a subquery, this is actually a very simple SQL:

Code:

 SELECT ANZSUCHEN.KANTON, COUNT(ANZSUCHEN.KANTON) 
FROM ANZSUCHEN
GROUP BY ANZSUCHEN.KANTON
ORDER BY ANZSUCHEN.KANTON

Here, the GROUP BY will take care of the DISTINCTness of the data.
Oh, and you can use the CASE statement to label the first field as you like. It still should not affect the count per value of ANZSUCHEN.KANTON. That is,

Code:

 
SELECT CASE
WHEN ANZSUCHEN.KANTON = '' THEN 'nicht_zugeteilt'
WHEN ANZSUCHEN.KANTON = '----------------------------------' THEN 'nicht_zugeteilt'
WHEN ANZSUCHEN.KANTON = 'AG' THEN 'mitte'
...
	 when ANZSUCHEN.KANTON = 'ZH' then 'ost'
END As Region,
COUNT(ANZSUCHEN.KANTON) 
FROM ANZSUCHEN
GROUP BY ANZSUCHEN.KANTON
ORDER BY ANZSUCHEN.KANTON

-Blasé

August 27th, 2004, 02:34 AM

djavet

Contributing User

Join Date: Apr 2004

Posts: 31

Time spent in forums: 59 m
Reputation Power: 5

Thx for your help.
I appreciate.

Regards, Dom

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: