Hi all,
I have been working on a report that does distinct counts from the database. The sql below works OK and brings me back the distinct count of all assets. I want to group by region "AST_Asset"."Region" but cant get the syntax right.
(select distinct count("AST_Asset"."Asset_ID_")
from AST_Asset
where "AST_Asset"."Type" = 'ROUTER'
and
("AST_Asset"."Status" = '6' or "AST_Asset"."Status" = '0') )
LB gave me this (thanks LB)
(select distinct count("AKA"."Asset_ID")
from AST_Asset AKA
where "AKA"."Region" = "AST_Asset"."Region"
and
"AKA"."Type" = `Router`)
but I get the following
The column prefix AST_Asset does not match with a table name or alias name used in the query.
I have CR 10 and SLQ server, not oracle or sybase, so the " and ' are different. I am trying to get the following list with a percentage going down.
Region no.routers no.records percent
Essex 12 7 58.33
London 86 25 29.07
Can anyone help. I have trawled through manuals but can not find anything helpfull.
Thanks
I have been working on a report that does distinct counts from the database. The sql below works OK and brings me back the distinct count of all assets. I want to group by region "AST_Asset"."Region" but cant get the syntax right.
(select distinct count("AST_Asset"."Asset_ID_")
from AST_Asset
where "AST_Asset"."Type" = 'ROUTER'
and
("AST_Asset"."Status" = '6' or "AST_Asset"."Status" = '0') )
LB gave me this (thanks LB)
(select distinct count("AKA"."Asset_ID")
from AST_Asset AKA
where "AKA"."Region" = "AST_Asset"."Region"
and
"AKA"."Type" = `Router`)
but I get the following
The column prefix AST_Asset does not match with a table name or alias name used in the query.
I have CR 10 and SLQ server, not oracle or sybase, so the " and ' are different. I am trying to get the following list with a percentage going down.
Region no.routers no.records percent
Essex 12 7 58.33
London 86 25 29.07
Can anyone help. I have trawled through manuals but can not find anything helpfull.
Thanks