Need help on query - math

# Thread: Need help on query - math

1. Jim
Senior Member
Join Date
Dec 1969
Posts
484

## Need help on query - math

Here&#039;s the short version. I&#039;ve got a table with two fields, customerid(Identity aka AutoNumber) and status, an integer describing active(1), VIP(3), inactive(5) or other(7).<BR>What I want to do is return a count of the customerIDs that are of each type. I know I can:<BR>select count(customerid) where status=# where #=the status. Is it possible to retrieve a count of customers for each status type in one SQL statement?<BR>TIA

2. James W. Guest

## RE: Need help on query - math

Try:<BR>select count(customerID),status from myTable group by status

3. Senior Member
Join Date
Dec 1969
Posts
1,849

## Yeah...

Dude this is a *****, I fiddled with the same query for quite some time and as soon as I figured it out, I was like "***" that was too easy...<BR><BR>SQL = "Select a.customerid, (select count(b.status) from tablename b where a.customerid = b.customerid group by b.customerid) from tablename a group by a.customerid order by 2 desc"<BR><BR>Give that a whirl, you may have to dink with it, but that is the same query I used (Different table names, and fields, but same logic)<BR><BR>-- Whol

4. Senior Member
Join Date
Dec 1969
Posts
1,849

## Well \$#^&!!!

I went about it the hard way... My brain is fried... When I wrote my query, I had just gotten off a kick of counting records in one table, in relation to another, where the only relationship between the two tables is a third table of keys... By the time I wrote the query, I was ready for a beer...<BR><BR>-- whol

5. Jeremy_D Guest

## RE: Yeah...

That will work, but you can optimize it by using a join:<BR><BR>SELECT a.customerid, Count(b.status) AS TotalCount<BR> FROM aTable a LEFT JOIN bTableb ON a.customerid = b.customerid<BR> GROUP BY a.customerid<BR> ORDER BY TotalCount<BR><BR>This way you can prevent the use of a correlated sub-query. If you have your indexes in order - an index on aTable(customerid) and bTable(customerid) seems about right - this will give you better performance.<BR><BR>Cheers,<BR>Jeremy_D<BR>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•