Hi all
I really need help with an SQL count query.
Basically I need to count multiple columns and then return the results. I have a repeat region and can count instances from one column i.e:

Town | Number
Swindon | 2
Bristol | 1
Reading | 4

etc

But I need to count within those results i.e:

Town | Number | Sales | IT | Accounts
Swindon | 2 | 1 | 1 | 0
Bristol | 1 | 0 | 0 | 1
Reading | 4 | 3 | 1 | 0

So the last 3 cloumns display what the 'number' column is made up of

My query so far is:
SELECT Town, COUNT(*) AS numberof
FROM tblorders
GROUP by Town
ORDER BY Town

I need something like:
SELECT Town, COUNT(*) AS numberof, COUNT(select type from orders where type = 'sales') as sales, COUNT(select type from orders where type = 'IT') as IT, COUNT(select type from orders where type = 'accounts') AS accounts
FROM tblorders
GROUP by Town
ORDER BY Town

But nothing I have tried works.

Please has anyone got any ideas!

Andy

<pre id=code><font face=courier size=2 id=code>select Town
, count(*) as numberof
, sum(case when type = 'sales'
then 1 else 0 end) as sales
, sum(case when type = 'IT'
then 1 else 0 end) as IT
, sum(case when type = 'accounts'
then 1 else 0 end) as accounts
from tblorders
group by Town
order by Town</font id=code></pre id=code>

rudy
r937.com/