how to use the order by and aggregate function together in sql query

Question

SELECT
    count(distinct req.requirementid),
    req.requirementid,
    org.organizationid,
    req.locationofposting,
    org.registereddate
FROM OrganizationRegisteredDetails AS org,
    RequirementsDetailsforOrganization AS req
WHERE org.organizationid = req.requirementid
ORDER BY
    org.RegisteredDate desc

this shows me the error :

Column 'RequirementsDetailsforOrganization.RequirementID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

how to do the 'order by org.RegisteredDate desc' in this Query ....

pls help me out.....??????

Answer 1

You need to add all columns referenced in the SELECT list or in the ORDER BY into a GROUP BY clause.

SELECT 
    count(distinct req.requirementid), 
    req.requirementid, 
    org.organizationid, 
    req.locationofposting, 
    org.registereddate 
FROM OrganizationRegisteredDetails AS org, 
    RequirementsDetailsforOrganization AS req 
WHERE org.organizationid = req.requirementid 
GROUP BY
    req.requirementid, 
    org.organizationid, 
    req.locationofposting, 
    org.registereddate 
ORDER BY 
    org.RegisteredDate desc 

Although in this case, you'll only get the value 1 in the first column in all rows since you're grouping on req.requirementid!

Answer 2

Error is there because as per th rule when your using aggregate function with group by clause you have to include the columns which are in your select list

following may help you to achieve functionality you want

   (select count(distinct requirementid),requirementid,
     from 
    RequirementsDetailsforOrganization
    group by requirementid) d

    inner join 

    (   SELECT req.requirementid, org.organizationid,

req.locationofposting,org.registereddate FROM OrganizationRegisteredDetails AS org, 

RequirementsDetailsforOrganization AS req WHERE org.organizationid =req.requirementid order by org.RegisteredDate desc) d1
   on 
    d.requirementid= d1.requirementid