Results 1 to 3 of 3

Thread: Grouping

  1. #1
    Join Date
    Sep 2012
    Posts
    1

    Unanswered: Grouping

    I have table TABLE1, with numeric datas in column billedCalls (in second) as follows:
    4
    6
    61
    22
    3
    1
    120
    181

    Now, I want sum of all datas under ReceivedCalls column but only those data with more than 5 second in ChargedCalls column.In addition, I want to make a group such that, 5-60 should be listed as 1 CALL, 61-120 as 2 CALL and eventually sum of all total CALL.
    Thanks.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    create another table that identifies the groups
    join to that table to get the grouping and sort on that grouping

    alternatively you could probably use a case statement to create the group ID, however that would require additional maintenance should the group requirement change
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    I think I get what you are looking for. Basically there are 3 columns in the table you mentioned: billCalls, chargedCalls and ReceivedCalls. You want to sum up the chargedCalls column when chargedCalls > 5 seconds. You also want to group these together based on the durations 5-60, 61-120, 121-180 ...

    If we look at the grouping you will notice that this is based on 60 second intervals starting at 0. So if we get (chargeCalls-1) DIV 60 this will give us the interval number starting with 0 => 5-60, 1=> 61-120, 2=>121-180 ...

    Here is an example:

    Code:
    mysql> select * from phonecalls;
    +----+-------------+--------------+
    | id | chargedCall | receivedCall |
    +----+-------------+--------------+
    |  1 |           3 |           10 | 
    |  2 |          45 |           20 | 
    |  3 |           4 |           21 | 
    |  4 |          33 |           43 | 
    |  5 |          60 |          123 | 
    |  6 |          61 |          135 | 
    |  7 |          68 |           43 | 
    |  8 |         125 |          234 | 
    |  9 |         128 |          932 | 
    +----+-------------+--------------+
    9 rows in set (0.00 sec)
    
    mysql> insert into phonecalls(chargedCall, receivedCall) values (5, 17);
    Query OK, 1 row affected (0.00 sec)
    
    mysql> select chargedCall, (chargedCall-1) DIV 60 as period from phonecalls where chargedCall >= 5;
    +-------------+--------+
    | chargedCall | period |
    +-------------+--------+
    |          45 |      0 | 
    |          33 |      0 | 
    |          60 |      0 | 
    |          61 |      1 | 
    |          68 |      1 | 
    |         125 |      2 | 
    |         128 |      2 | 
    |           5 |      0 | 
    +-------------+--------+
    8 rows in set (0.00 sec)
    
    mysql> select (chargedCall-1) DIV 60 as period, sum(receivedCall) from phonecalls where chargedCall >= 5 group by (chargedCall-1) DIV 60;
    +--------+-------------------+
    | period | sum(receivedCall) |
    +--------+-------------------+
    |      0 |               203 | 
    |      1 |               178 | 
    |      2 |              1166 | 
    +--------+-------------------+
    3 rows in set (0.00 sec)
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •