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

Somewhat like



The expression



as stated, does not make any sense. I assume that you wish to disqualify those two types from the result.

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.....

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:

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.

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!

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;

Hello, anyone?

I think I have kind of got the gistm but wanted some steer.

Looks pretty good to me