Jump to content

Custom query for count if against three tables...


chadrt
 Share

Recommended Posts

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'

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.