SQL:Group By

All column names in SELECT list must appear in GROUP BY clause unless name is used only in an aggregate function


http://stackoverflow.com/questions/5986127/do-all-columns-in-a-select-list-have-to-appear-in-a-group-by-clause


Include non-aggregate column in group by clause (with a slight wrinkle)

http://stackoverflow.com/questions/8849552/include-non-aggregate-column-in-group-by-clause-with-a-slight-wrinkle

Getting summarizing values

http://www.sql-ex.ru/help/select4.php

----------
Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause
http://stackoverflow.com/questions/6456727/column-is-invalid-in-the-select-list-because-it-is-not-contained-in-either-an-ag


;WITH cte AS
(
 SELECT *
 FROM GREScores g
 WHERE g.applicationID = 1
)
SELECT 
     g.applicationId,
     -- (another 100 or so columns just like above)
    AScore =(select  max(g2.AScore) FROM cte g2) ,
    APercentile =(select  max(g2.APercentile) FROM cte g2)
FROM cte g

----------
T-SQL GROUP BY: Best way to include other grouped columns

http://stackoverflow.com/questions/626788/t-sql-group-by-best-way-to-include-other-grouped-columns

You can get it to work with something around these lines:

select e.empID, fname, lname, title, dept, projectIDCount
from
(
   select empID, count(projectID) as projectIDCount
   from employees E left join projects P on E.empID = P.projLeader
   group by empID
) idList
inner join employees e on idList.empID = e.empID

This way you avoid the extra group by operations, and you can get any data you want. Also you have a better chance to make good use of indexes on some scenarios (if you are not returning the full info), and can be better combined with paging.
~~~

 select e.empID, e.fname, e.lname, e.title, e.dept, p.projectIDCount
    from employees e 
   inner join ( select projLeader, count(*) as projectIDCount
                  from projects
                 group by projLeader
              ) p on p.projLeader = e.empID