MySQL Crosstab using and IP Range

Hi,

I have two MySQL tables - let's say they are set up like this:

TABLE : OBJECT

+----------------------+---------------------+------+-----+---------+-------+
| Field                | Type                | Null | Key | Default | Extra |
+----------------------+---------------------+------+-----+---------+-------+
| REQID                | bigint(20) unsigned |      | PRI | 0       |       |
| IP                   | int(10) unsigned    | YES  | MUL | NULL    |       |
+----------------------+---------------------+------+-----+---------+-------+


TABLE : TS
+------------+----------------------+------+-----+---------+-------+
| Field      | Type                 | Null | Key | Default | Extra |
+------------+----------------------+------+-----+---------+-------+
| REQID      | bigint(20) unsigned  |      | PRI | 0       |       |
| TARS       | varchar(80)          |      | PRI |         |       |
| CLICKS     | smallint(5) unsigned |      | MUL | 0       |       |
+------------+----------------------+------+-----+---------+-------+

When used together they are joined using a left join on the OBJECT table.  For example,

          SELECT
            TARS AS TARS
            ,COUNT(DISTINCT OBJECT.REQID) AS requests
            ,SUM(IF(TS.CLICKS>0,1,0)) AS clicks
        FROM
            OBJECT
        LEFT JOIN TS ON (OBJECT.REQID = TS.REQID)
        GROUP BY TARS
            
This Query gives a good summary of usage of each TARS eg:

      TARS                  Requests                  Clicks
      TARS1                  44308                        32465
      TARS2                  43955                        8020
      TARS3                  76582                        9759

However, I want to create a crosstab which would contain the same data as the above except also show the location:

      TARS                  USERS_Requests            USERS_Clicks            STAFF_Requests            STAFF_Clicks
      TARS1                  763                              1234                        43545                        31231
      TARS2                  432                              6789                        43523                        1231
      TARS3                  7                              5435                        76575                        4324
      
Given that I know the numerical range of the IPs for (although, I've made up the following):

      USERS:
                  1266723156 to 1266723172
                  and 1266723222 to 1266723322
                  
      STAFF:
                  1318540336 to 1318540456
                  and 1318540499 to 1318540511
                  
How can I build these IP ranges into the query above to further split my data by IP groups?

Hopefully that all makes some sense!
cantthinkofoneAsked:
Who is Participating?
 
racekConnect With a Mentor Commented:

 SELECT TARS AS TARS,
COUNT(DISTINCT 
IF(IP BETWEEN 1266723156 AND 1266723172, OBJECT.REQID,
IF(IP BETWEEN 1266723222 AND 1266723322, OBJECT.REQID, NULL)))requests_users,
IF(IP BETWEEN 1318540336 AND 1318540456, OBJECT.REQID,
IF(IP BETWEEN 1318540499 AND 1318540511, OBJECT.REQID, NULL))) required_staff,
SUM( 
IF(IP BETWEEN 1266723156 AND 1266723172 AND TS.CLICKS>0,1, 
IF(IP BETWEEN 1266723222 AND 1266723322 AND TS.CLICKS>0,1,0))) clics_users,
IF(IP BETWEEN 1318540336 AND 1318540456 AND TS.CLICKS>0,1, 
IF(IP BETWEEN 1318540499 AND 1318540511  AND TS.CLICKS>0,1,0))) clic_staff
FROM OBJECT
 LEFT JOIN TS ON OBJECT.REQID = TS.REQID
GROUP BY TARS

Open in new window

0
 
cantthinkofoneAuthor Commented:
Brilliant - I had overlooked the possibility of a nested if!  Thanks so much.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.