I have two tables. One is a list of Sites [tblSite]. The other is a
list of Tasks per Site [tblTask].
CREATE TABLE tblSite (
	SiteOid varchar (32) PRIMARY,
        SiteName varchar (255) 
); 
CREATE TABLE tblTask (
	TaskOid varchar (32) PRIMARY,
	SiteOid varchar (32),
	StatusCode varchar (3)
); 

StatusCode field can have values 'INC','CMP','EXP'

I want a list of sites with how much work is done on each and how much
is the total. So I want to have a SELECT statement that would give me
SiteName with (count of all the the tasks with StatusCode 'CMP' OR
'EXP') and (total count of all the tasks for that site).

Example output would be:
|SiteName          |Progress  |
|------------------+----------|
|site_A            |0/20      |
|site_B            |10/40     |
|site_C            |10/10     |

We are using "SQL Server CE" and the SQL commands reference is here,
http://msdn.microsoft.com/library/en-us/sqlce/htm/_lce_sql_overview.asp?frame=true

I would test the proposed answer on our "SQL Server CE" database
before accepting it. Please feel free to ask clarifying questions.

Does this database support stored procs?  It's easy to create a proc
to do this.  I don't know why you would want to do it all in one
select statement.

Thank you for your attention. Sadly "SQL Server CE" does not support
"CREATE PROCEDURE" command.

I am aware that I can do this in two steps. Step 1 is to populate a
temporary table with the nested select statement results. Step 2 to
join with that temporary table to get the desired result. However I
believe there must be a more optimized way of do this, hence I'm
looking for someone smart to figure out how to do this in one sql
statement.

Thank You,
alitech-ga

Just because you do it one statement does not necassarily imply that
it will run faster.  That is highly dependant on the db optimizer. 
The only way to know for sure is to try both ways and benchmark them.

Limitations, limitations! "SQL Server CE" does not support 
'INSERT INTO table1 VALUES ( val1, val2 ), ( val3, val4 )' --or-- 
'INSERT INTO table1 VALUES ( val1, val2 );INSERT INTO table1 VALUES (
val3, val4 );' --or--
'INSERT INTO table1 VALUES ( SELECT * FROM table2 )'

So for me to populate the temporary table I got to have a loop in my
application. So the control jumps between the application-layer and
SqlServerCE-layer for each row. If I use a SQL command then the
control will stay in the SqlServerCE-layer until the query is done.
Hence I think it will be faster than switching context every time.

FYI "SQL Server CE" does not support nested select statements.

SQL Server CE sounds a bit difficult to work with.

Hence calling it a "Challenge"!

hi alitech-ga,

you need to sql derived tables. here is the sql for it.

SELECT 
	a.SiteName, 
	CONVERT(NVARCHAR(10),CASE WHEN b.InProgressTasks IS NULL THEN '0'
ELSE b.InProgressTasks END) + '/' + CONVERT(NVARCHAR(10),CASE WHEN
c.TotalTasks IS NULL THEN '0' ELSE c.TotalTasks END) AS Progress
FROM 
	Site a 
	LEFT OUTER JOIN
	(
		SELECT a.SiteID as SiteID, COUNT(*) as InProgressTasks FROM Site a
JOIN Task b ON (a.SiteID = b.SiteID) WHERE b.StatusCode IN
('CMP','EXP') GROUP BY a.SiteID
	) b ON (a.SiteID = b.SiteID)
	LEFT OUTER JOIN
	(
		SELECT a.SiteID as SiteID, COUNT(*) as TotalTasks FROM Site a JOIN
Task b ON (a.SiteID = b.SiteID) GROUP BY a.SiteID
	) c ON (a.SiteID = c.SiteID)

if this is not what you are expecting, let me know in more detail.

thanks,
sazid.

Dear Sazid,

Thank you for your work. However nested select are not supported, so I
can't do "...LEFT OUTER JOIN ( SELECT..."

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlce/htm/_lce_from_clause.asp

Cheers,
Ali

Does SQL CE support the group by clause?  If so the answer would be simple:

select s.sitename,
 convert(nvarchar(10), sum(case t.statuscode when 'INC' then 0 else 1 end))
 + '/' +
 convert(nvarchar(10), count(*)) as progress 
from tblsite s
join tbltask t on s.siteoid = t.siteoid
group by s.sitename

Yes, Yes, and YES! gdunbar-ga you did it, Thank you! 

Can you place it in the Answer section so I can credit you.

Thanks again,
Ali

Unfortunarely I'm not an official "researcher", so it's a freebie. 
Glad I could help.  You can cancel your question to avoid paying.

Wow gdunbar-ga, I must say I really appreciate your help and the SQL
trick you taught me.

Thank you,
Ali