Need help on query - math

Results 1 to 5 of 5

Thread: Need help on query - math

  1. #1
    Join Date
    Dec 1969

    Default 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. #2
    James W. Guest

    Default RE: Need help on query - math

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

  3. #3
    Join Date
    Dec 1969

    Default 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. #4
    Join Date
    Dec 1969

    Default 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. #5
    Jeremy_D Guest

    Default 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