Custom query for count if against three tables...

[chadrt]

In my db I have 2 tables I need to pull data from and run a count if somehow.  The db_cust table has all the customer data.  The db_devices has a few thousand entries on devices that are either being billed, promotional, cancelled etc.  So I am looking for a way to have a single query that will look at the db_devices and count each device that has a "billable" example below...

CUSTOMER NAME   -   BILLABLES
Test Customer     -                   5
Great Customer    -                 38
Really Great Customer      -   235
 

it would look at db_devices.agencyname and count each instance of where that field contains a "1" as that is the billable int.

 

customer 4567 (Test Customer) has 38 devices listed but some are cancelled, lost and some are free for being nice etc etc and 5 are billable it would return a 5 for the count if bill_type='1'

[mac_gyver]

the following is the SELECT query prototype definition, with the elements you will need in bold -

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
      [HIGH_PRIORITY]
      [STRAIGHT_JOIN]
      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
      [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr ...]
    [FROM table_references
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [PROCEDURE procedure_name(argument_list)]
    [INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
      | INTO DUMPFILE 'file_name'
      | INTO var_name [, var_name]]
    [FOR UPDATE | LOCK IN SHARE MODE]]

the select_expr would include a COUNT() term to get the number of matching rows per group, along with any other columns you want in the result.

the FROM table_references would include a JOIN between the two (or is it three as the title says) tables.

the WHERE where_condition would match the column and value for billable items.

the GROUP BY col_name would group by the customer id/name column.

the ORDER BY col_name would sort the results the way you want, such as by the customer name.

give this a try.