SELECT a column not in GROUP BY clause

user_863361 0
Viewed 2188

I'm trying to write a query that would select only the rows that have events which where the only events in that year.

Eg:

Year   Event
2011     A
2011     B
2012     C
2013     B
2013     D
2014     D

So, I would like to get the rows 2012 C and 2014 D in the results. I tried doing a GROUP BY on Year, but that wouldn't let me select the Event column. 2011 and 2013 have 2 events, so these shouldn't be in the results.

Please help.

EDIT: I could write a nested query to get the only the rows having count(Year) = 1 with GROUP BY Year, but I'm unable to get the Event column selected in the outer query

SELECT Year, Event from table where Year in (SELECT Year from table GROUP BY Year Having count(*) = 1) as count;
mysql database
5 Answers

There is no need for using a subquery or nested query. You can simply GROUP By Year field and use HAVING COUNT(Year)=1 to find the required rows. So, the applicable query will be:

SELECT Year, Event
FROM table_name
GROUP BY Year
HAVING COUNT(Year)=1

You can find the executable solution sample at:

http://sqlfiddle.com/#!9/b47044/11

Logic: When you group by Yearit aggregates all rows with same year. So, count will be 2 for 2011.

You can check this by running:

SELECT Year, Event, COUNT(Year) as event_count
FROM table_name
GROUP BY Year

You can see this intermediate step in execution, at: http://sqlfiddle.com/#!9/b47044/10

This above solution will only work for MySQL version < 5.7. For higher versions find the solution below.

For 5.7 and greater the ONLY_FULL_GROUP_BY SQL mode is enabled by default so this will fail. Either you can update this mode( Refer answers under SELECT list is not in GROUP BY clause and contains nonaggregated column .... incompatible with sql_mode=only_full_group_by ) or alternatively you can use ANY_VALUE() function to refer to the non-aggregated column, so update query that will work in MySQL 5.7 and greater is:

SELECT Year, ANY_VALUE(Event)
FROM table_name
GROUP BY Year
HAVING COUNT(Year)=1;

You can find executable example at: https://paiza.io/projects/e/tU-7cUoy3hQUk2A7tFfVJg

Reference: https://docs.oracle.com/cd/E17952_01/mysql-5.7-en/miscellaneous-functions.html#function_any-value

user_7492320

You have a minor mistake in the query, the count(*) which is used in having clause should also be in the select clause

SELECT Year, Event from table where Year in ( SELECT Year from ( SELECT Year,count(*) from table GROUP BY Year Having count(*) = 1)temp );

user_60843220

Only those Year and events need to be filtered which contains single event that Year

  1. Inner Query would give you only years which have one event
  2. Outer query would select the events of those years 
SELECT Year, Event from table where Year in 
(SELECT Year from table GROUP BY Year Having count(*) = 1);
user_116079860

Good Question,

You don't even need a subquery to get the desired output. Concatenate all the event names into one string, then search for comma , in the string, If comma , is found, this year has more than one events, otherwise only one.

   SELECT Year, GROUP_CONCAT(Event) AS Event FROM Events GROUP BY (year) having 
   INSTR(Event, ",") = 0;
user_62893620 
SELECT Year, Event 
FROM table 
WHERE Year in (SELECT Year 
               FROM table 
               GROUP BY Year 
               HAVING count(*) = 1);
user_91805060
Related