Queries that Aggregates Counts of Unique Values into a field 1

[mekesse]

I have a 40,000 record database which consists of the following fields:

ID, STATE, TYPE

The type field contains one of 5 distinct values.

I want to run a query where I group the records by state and also get counts of each distinct type value. The resulting output I want is as follows:

STATE, TYPE_A, TYPE_B, TYPE_C, TYPE_D, TYPE_E, TYPE_ALL

type_all is a total count of records for that state.

My SQL Thus far looks like this:

SELECT TABLE1.STATE,
COUNT(TABLE1.type) as TYPE_ALL,;
COUNT(TABLE1.type='A') as TYPE_A,;
COUNT(TABLE1.type='B') as TYPE_B,;
COUNT(TABLE1.type='C') as TYPE_C,;
COUNT(TABLE1.type='D') as TYPE_D,;
COUNT(TABLE1.type='E') as TYPE_E;
FROM TABLE1;
GROUP BY TABLE1.STATE

However, all count values are the same. Any ideas of what I'm doing wrong and how I might fix it?

Thanks, in advance, for your help!

Michael

 

[Diederik]

Can't tell top of my head what's wrong with the SQL stement.
However, I do have an alternative:

SELECT TABLE1.state, ;
	SUM( IIF(TABLE1.type = "A", 1, 0) ) AS type_a, ;
	SUM( IIF(TABLE1.type = "B", 1, 0) ) AS type_b, ;
	SUM( IIF(TABLE1.type = "C", 1, 0) ) AS type_c, ;
	SUM( IIF(TABLE1.type = "D", 1, 0) ) AS type_d, ;
	SUM( IIF(TABLE1.type = "E", 1, 0) ) AS type_e  ;
  FROM TABLE1 ;
 	GROUP BY TABLE1.state ;
  INTO CURSOR result

Should give the desired result...
Diederik Vermeeren
verm1864@exact.nl

 

[weedz]

I guess in your original SQL statment you should add an ORDER BY.

HTH,

Weedz (Wietze Veld)
veld4663@exact.nl

They cling emotionally to code and fix development rather than choosing practices based on analytical assesments of what works best.

After the GoldRush - Steve McConnell

 

[SureshMukhi]

Use the query designer and create a CROSS TAB query grouped by STATE and TYPE. That way, you don't have to hard-code each TYPE in your query and should you add more TYPEs, you don't have to change your code.