Nested select ...any help ?

Alberto Silva, MVP

2009-07-16 09:33:52 UTC

Hi,
Try this way:

SELECT count(*) as Column1, alias2.recordcount as Column2, column3
FROM table1 AS alias1 LEFT JOIN
(SELECT column3, count(*) AS recordcount FROM Table1 GROUP BY column3
WHERE cond='X' ) AS alias2
ON alias1.column3 = alias2.column3
GROUP BY column3
ORDER BY Column1 Desc

It may not work at the first try, I have not tested the instruction but this
syntax is supposed to work. Beware that, at least on Windows Mobile/CE
devices, it may be more performant to break the instruction in two, getting
the 2nd count(*) with a prepared ExecuteScalar command while iterating over
the SELECT results.
--
Alberto Silva
http://www.moving2u.pt - R&D Manager
http://msmvps.com/AlbertoSilva - Blog
Microsoft MVP - Device Application Development

Post by Alfaking
Hi,
I'm trying this
"select count(*) as Column1,
(select count(*) from Table1 where Table1.Cond = 'X'
and Table1.column3=alias1.column3 ) as Column2,
Column3
from table1 as alias1 group by Column3 order by column1 Desc"
It works fine in sql server 2005, but it's not working in sql ce 3.5
sp1.
the result is
column1|column2|column3 --> from 1 table, but with 2 different
conditions
this should be used in a vb.net application.
any help ?
__________ Information from ESET NOD32 Antivirus, version of virus
signature database 4248 (20090716) __________
The message was checked by ESET NOD32 Antivirus.
http://www.eset.com

__________ Information from ESET NOD32 Antivirus, version of virus signature database 4249 (20090716) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com