Help to produce a slicker query......

[mdemetri2]

Hi, quite a novice at queries at the moment, am wanting to create summaries primarily based on counts (and in the future sums). The query below is a start at this:

 

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'm sure this can be made slicker (somehow) any advice? Also, at the moment I specifcy a region, but would like not to and have it provide the counts for each, but if I dont have the region it does come back with each region but the row counts are the same for each - just the count of all.

[Jessica]

Joins and Group By

[Barand]

and type != 'colo' or 'Data Centre'

 

Wrong syntax.

 

AND type NOT IN ('colo', 'Data Centre')

[mdemetri2]

Jesirose - ok, but in any particular way, so instead of using 'where' do a join.....

 

Barand - thanks, it worked even with !=, but will use NOT IN....

 

 

[Jessica]

The or 'DataCenter' line does nothing. it doesn't "work" it just didn't throw up an error.

[mdemetri2]

Done using this in the end:

 

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;