November 30, 2007 at 3:08 am
Folks
I have a query with error 🙁
ssql = "SELECT b.pn_bnc, b.model_bnc, Count(b.pn_bnc) AS Bounce, (SELECT COUNT(Bounce.laudo_new) FROM Bounce WHERE (Bounce.laudo_new = 'Sem defeito') AND (Bounce.bnc90d = b.bnc90d)) AS NTF FROM Bounce AS b WHERE(((b.bnc90d) = 'VERDADEIRO')) GROUP BY b.pn_bnc, b.model_bnc, b.bnc90d"
when I run the query, for "NTF" return the sum but isn't group by pn_bnc
what can be?
Tks!
November 30, 2007 at 3:48 am
It would actually group the rows by columns b.pn_bnc, b.model_bnc and b.bnc90d. And also you have a sub-query executing for each row, will not scale well in production.
Here is another way do this...
SELECTb.pn_bnc, b.model_bnc, b.bnc90d, COUNT( b.pn_bnc ) AS Bounce,
COALESCE( t.Total, 0 ) AS AS NTF
FROMBounce b
LEFT JOIN
(
SELECTbnc90d, COUNT( Bounce.laudo_new ) AS Total
FROMBounce
WHERElaudo_new = 'Sem defeito'
GROUP BY bnc90d
) t on b.bnc90d = t.bnc90d
WHEREb.bnc90d = 'VERDADEIRO'
GROUP BY b.pn_bnc, b.model_bnc, b.bnc90d, t.Total
--Ramesh
November 30, 2007 at 5:25 am
Tks for answer
but I don't how why ... when run the query faile!
I'm using BD access, theses comand suport th access?
this query I'm runing in the VB.net hehe
this is error IErrorInfo.GetDescription failed with E_FAIL(0x80004005).
🙁
November 30, 2007 at 5:34 am
The solution provided is for SQL Server 2000 or higher and wouldn't run in MS Access or any other RDBMS.
--Ramesh
November 30, 2007 at 6:11 am
Thanks for answer again!
I will wait to news answer hehe
Tks!
November 30, 2007 at 12:10 pm
Or, you could try posting in the "Access" forum 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply