COUNT number of returned rows 1

[slowfish]

Hi

My query returns all the records of "active" London customers who have ordered within the past year. I also need to know how many customers, but where do I put the COUNT function?

SELECT *, MAX(customers_orders.date) AS maxdate,
customers.id AS refid,

FROM customers

LEFT JOIN customers_orders ON customers.id = customers_orders.custid

WHERE archived = 'no' AND city = 'London'

GROUP BY customers.id

HAVING maxdate >= DATE_SUB(NOW(),INTERVAL 1 YEAR)

Thanks in advance
slowfish

 

[r937]

the answer is to use whatever function is available in your application language for this purpose (in php i believe it's called mysql_num_rows() or something similar)

failing that, just run another query, putting your original query inside a simple SELECT COUNT(*)

SELECT COUNT(*) AS number_of_customers
  FROM ( SELECT /* no dreaded, evil "select star" */
                MAX(customers_orders.date) AS maxdate     
              , customers.id AS refid /* no comma */
           FROM customers
         INNER    /* not LEFT */
           JOIN customers_orders 
             ON customers_orders.custid = customers.id
          WHERE customers.archived = 'no' /* qualify your columns */
            AND customers.city = 'London' /* qualify your columns */
         GROUP 
             BY customers.id
         HAVING maxdate >= CURRENT_DATE - INTERVAL 1 YEAR
       ) AS q

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[slowfish]

That's great, thanks very much. The query inside the query worked. I'm using Perl but not sure what the equivalent of mysql_num_rows() is. Any ideas?

 

[r937]

$number = $query->numrows;

10 seconds on google



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[slowfish]

Thanks, but this doesn't seem to work with all drivers - it just returns -1 -

http://www.perlmonks.org/?node_id=561408

I'll stick with the query inside the query and get sql to do the work.