0

I am trying to get the sum of a column that is filtered by another column (distinct values of that column). I have tried "group by" "distinct" "with rollup" and many other variations. I have a bunch of columns showing a customer link. Most services have a redundant side, though some don't have redundancy. I am trying to get a bandwidth total for entire "fnn" column but excluding the redundant side value if it exists. If there is redundancy, then it will be indicated by having the same fnn (and same bandwidth value).

I have been struggling with this for ages. Any help would be fantastic. Solution in mysql or php.

my table looks like:

table name = cust_child

    id | cust_name |cp_bw|cp_fnn |other_columns  
    1  | A         | 30  | 11    |xxx   
    2  | A         | 30  | 11    |xxx 
    3  | B         |100  | 22    |xxx
    4  | B         |100  | 22    |xxx
    5  | C         |50   | 33    |xxx

Result should look like:

    Total bandwidth = 180
Nigel
  • 135
  • 7
  • The answer you want can be found here http://stackoverflow.com/q/12475850/1450077 - it's a lengthy Q&A I wrote on SQL that certainly covers aggregate functions and how to use a where clause :) – Fluffeh Feb 18 '15 at 10:39
  • Thanks heaps for this - A lot of info to get my head around :) – Nigel Feb 18 '15 at 12:32

1 Answers1

1
SELECT SUM(c.cp_bw) AS bandwith
FROM cust_child c
 INNER JOIN (
   SELECT c.id
   FROM cust_child c
   GROUP BY c.cp_fnn
 ) t1 ON (t1.id = c.id)

http://sqlfiddle.com/#!2/01219/1

arnoudhgz
  • 1,284
  • 9
  • 17