a select SQL uses join,subquery,and group by,PROBLEM

  • --------------------

    SELECT id_tender,count(*) as no_of_app FROM applytender WHERE id_tender IN (select id from tenders where owner1 = 2) GROUP BY id_tender

    --------------------

    above sql query is OK and will return (example) :

    id_tender | no_of_app

    1 | 3

    3 | 6

    but above query is not useful as the results of id_tender column are foreign key values . So when change it to the below query (using Inner Join)to retrieve its details from another table , tenders :

    -------------------------------

    SELECT applytender.id_tender,tenders.titlename,count(*) as no_of_app FROM applytender JOIN tenders ON applytender.id_tender=tenders.id WHERE id_tender in (select id from tenders where owner1 = 2) GROUP BY id_tender

    ------------------------------

    an error message displays in the output box , saying ; "tenders.titlename contains no aggregate functions....."

    What's the problem? How to fix this query ?

    The results I wanted are suppose to be like this:

    id_tender | titlename | no_of_app

    1 | building renovate | 3

    3 | supply stationery | 6

  • Does this one work?

    SELECT APP.id_tender

    ,COUNT(*) as no_of_app

    ,TEN.titlename

    FROM applytender APP

    JOIN tenders TEN ON APP.id_tender=TEN.id

    WHERE TEN.owner1 = 2

    GROUP BY APP.id_tender

    ,TEN.titlename

    Best Regards,

    Chris Büttner

  • Thank you very much.

    I finally know where I got it wrong . The simplified query you provided, the logic the way I wanted to, is right , thus the problem is solved.

    Can continue with my work now........

  • You have to include all columns you are including in the summary result, unless you are doing some kind of an aggregate on the column... thus the error message.

Viewing 4 posts - 1 through 3 (of 3 total)

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