Grouping with SQL expression field

[seabubble]

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

 

[rosemaryl]

Dear Seabubble,

When I get that message, it means that I am performing a correllated subquery and the field which I am using in the SQL expression is not on the report.

Add the "AST_Asset"."Region" field onto the report (you can suppress if you like) and see if that makes the error message disappear.

Regards,

ro

Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[synapsevampire]

You also might try removing the quotes entirely, rememebr that this is processed on the database side (technically by the connection driver), and this may have something to do with it.

-k

 

[seabubble]

Hi ro, and synapsevampire, thanks for replying. :0) I'm in England, depending on where you are replies may take time!

I have put the following in with and with out quotes.
(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')group by "HPD_HelpDesk"."Region" )

It goes off and takes ages then lists the total distinct count in each region.

1034 london
1034 helpdesk

I have the region in the details suppressed. Now the syntax is there, here is a siple what I am trying to do.
count the distinct assets and get the number or records raised for each region. then work out the percentage. Group by the percentage starting with the highest.

Region no.routers no.records percent
Essex 12 7 58.33
London 86 25 29.07