error query

  • 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!

  • 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


  • 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).

    🙁

  • The solution provided is for SQL Server 2000 or higher and wouldn't run in MS Access or any other RDBMS.

    --Ramesh


  • Thanks for answer again!

    I will wait to news answer hehe

    Tks!

  • Or, you could try posting in the "Access" forum 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply