February 10, 2008 at 12:49 pm
--------------------
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
February 10, 2008 at 12:59 pm
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
February 10, 2008 at 2:01 pm
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........
February 10, 2008 at 3:18 pm
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