#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2009
    Posts
    17
    Rep Power
    0

    Making query slicker - help / advice?


    Hi can anyone help in me making this a better query:

    select distinct Region,
    (SELECT count(*)
    FROM critical_risks
    WHERE propertyref in (select propertyref from locations where region = 'GB' and type != 'colo' or 'Data Centre') and classification in (2,3,4,5)) as IR,
    (SELECT count(*)
    FROM critical_risks
    WHERE propertyref in (select propertyref from locations where region = 'GB'and type != 'colo' or 'Data Centre') and classification in (7,8,9,10)) as OP,
    (SELECT count(*)
    FROM critical_risks
    WHERE propertyref in (select propertyref from locations where region = 'GB'and type != 'colo' or 'Data Centre') and classification in (12,13)) as MT,
    (SELECT count(*)
    FROM critical_risks
    WHERE propertyref in (select propertyref from locations where region = 'GB'and type != 'colo' or 'Data Centre') and classification in (15,16,17)) as C,
    (SELECT count(*)
    FROM critical_risks
    WHERE propertyref in (select propertyref from locations where region = 'GB'and type != 'colo' or 'Data Centre') and classification in (2,3,4,5,7,8,9,10,12,13,15,16,17)) as TotalRisks
    from locations where region = 'GB';


    I would actually like to not specifiy a region, but when I dont put it in although I get all the regions back the row counts are the same for each - bascially the totals. I know I just need to join and group some other way but not sure how or what!.

    Thanks
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,377
    Rep Power
    391
    Somewhat like

    Code:
    select locations.Region,
           sum(case when critical_risks.classification in (2,3,4,5) then 1 else 0 end) as IR,
           sum(case when critical_risks.classification in (7,8,9,10) then 1 else 0 end) as OP,
           sum(case when critical_risks.classification in (12,13) then 1 else 0 end) as MT,
           sum(case when critical_risks.classification in (15,16,17) then 1 else 0 end) as C,
           count(*)
      from locations
      join critical_risks
        on locations.propertyref = critical_risks. propertyref
       and critical_risks.classification in (2,3,4,5,7,8,9,10,12,13,15,16,17)
     where locations.type not in ('colo','Data Centre')
     group
        by locations.region
    The expression

    Code:
    and type != 'colo' or 'Data Centre'
    as stated, does not make any sense. I assume that you wish to disqualify those two types from the result.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2009
    Posts
    17
    Rep Power
    0
    Hi swampBoogie, I will give that a go and see what happens, thank you.

    In terms of the expression '!=' I wrote that to mean not equal to, your assumption is correct. In MySQL Query browser it works.....
  6. #4
  7. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Location
    Stockholm, Sweden
    Posts
    4,495
    Rep Power
    534
    Originally Posted by mdemetri2
    ...
    In MySQL Query browser it works.....
    It doesn't work, MySQL just doesn't report an error.
    And in this case the effect of this condition in the query is zilch.
    The query will return all rows all the time.

    Basically in MySQL a zero length string is interpreted as False and anything else is True.

    So you have written:
    Code:
    return all rows where:
    type not equals 'colo' (is either true or false)
    OR 'someString' (is the same as 1=1, i.e is always true)
    and since the second statement is always true all rows will always be returned from this condition regardless of if the first statement was true or false.
    /Stefan
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2009
    Posts
    17
    Rep Power
    0
    Thanks swampBoogie, you are a star!

    I have been trying to amend this somewhat, and although the IR, OR, MT and C split are ok, what if for each of those I wanted to do sum of a field estCost rather than count how many IR etc there are?

    So I would for each category (IR, OR, MT and C) get the sum of field estCost....

    I can crudley do it specifying only one classification:

    select sum(estcost) from critical_risks where classification = 2 and propertyref in (select propertyref from locations where type not in ('colo','Data Centre'));

    Any advice appreciated. I will continue to look at using the case when statement also......Cheers!
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2009
    Posts
    17
    Rep Power
    0
    How about this, is this right?

    select locations.region,
    sum(case when classification in (2,3,4,5) then estCost else 0 end) as IR_EstCost,
    sum(case when classification in (7,8,9,10) then estCost else 0 end) as OP_EstCost,
    sum(case when classification in (12,13) then estCost else 0 end) as MT_EstCost,
    sum(case when classification in (15,16,17) then estCost else 0 end) as C_EstCost
    from locations
    join critical_risks on locations.propertyref = critical_risks.propertyref
    where locations.type not in ('colo','Data Centre')
    group by locations.region;
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2009
    Posts
    17
    Rep Power
    0
    Hello, anyone?

    I think I have kind of got the gistm but wanted some steer.
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    Looks pretty good to me

IMN logo majestic logo threadwatch logo seochat tools logo