SQL Server CE Problem

[hoffmandirt]

I have this query

SELECT WORK_CENTER, COUNT(DISTINCT APL) AS related_apls, COUNT(DISTINCT NIIN) AS related_niins
FROM RM22_WC_DETAILS
GROUP BY WORK_CENTER

and it chokes on the count(distincts).

I have tried inline statements such as

SELECT COUNT(APL) AS related_apls
FROM (select distinct apl from RM22_WC_DETAILS)


Also no luck with this.

I am wondering if there is simple way to do a distinct count using SQL Server CE without creating seperate tables. I am using Server CE on a pocket pc.

[Krzemo]

Also no luck with this.
I think U forgot to give a name to derived table ..
SELECT COUNT(*) AS related_apls
FROM (select distinct apl from RM22_WC_DETAILS) MyDerivedTable

U can also try this:
SELECT COUNT(*) related_apls
FROM (select apl,COUNT(*) ile from RM22_WC_DETAILS) MyDerivedTable

Best regards,
Krzemo.

[hoffmandirt]

I'm not having much luck with those queries. They both choke on the second select statement. Also can you explain the MyDerivedTable. I'm not sure what that is for. I am assuming I can put any name I want there.

[Krzemo]

They both choke on the second select statementIf U post an actual SQL error it would be nice.....
Since I havn't CE server and can't test it by myself.

I am assuming I can put any name I want there. Yes U can put any name here, but U must put something - it is required.

Krzemo.

[hoffmandirt]

Select count(*) "Related APLs"
From (Select apl, count(*) ile from wc_Details) MyDerivedTable

Native Error: (25501)
Description: There was an error parsing the query. token line.....yada yada yada
Interface Defining Error:
Param. 0: 2
Param 1: 7
Param 2: 0
Param 3: Select


I get the same error for both queries. The query analyzer does not like the second select statement for both. note: i renamed rm22_wc_details to wc_details.

[Krzemo]

Try that way:

Select count(*) ra From (Select apl, count(*) ile from wc_Details GROUP BY apl) MyDerivedTable

[hoffmandirt]

no luck. It doesn't like the inline selects for some reason.