[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

MySQL Crosstab using and IP Range

Posted on 2009-04-27
2
Medium Priority
?
268 Views
Last Modified: 2012-05-06
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!
0
Comment
Question by:cantthinkofone
2 Comments
 
LVL 14

Accepted Solution

by:
racek earned 2000 total points
ID: 24247823

 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
 

Author Closing Comment

by:cantthinkofone
ID: 31575207
Brilliant - I had overlooked the possibility of a nested if!  Thanks so much.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question