CODEWITHSUNDEEP
mysqlgroup-by
dsol828
dsol828

Reputation: 413

Grouped Query with different field values

I use the following query to group year-by-year salaries into one single contract record.

SELECT playerid,contractid,term,min(season) as yearone,exp_year,notes
FROM salaries
GROUP BY playerid, contractid
HAVING yearone <= 2014 AND exp_year > 2014

As you can see, I group all records on playerid and contractid. All fields in the grouped result have the same data with the exception of "notes" which can either contain a note or be NULL.

What I would like to achieve is one of the following two options:

  1. If ANY of the records grouped together contain a note, indicate that not all records were NULL (can be as simple as a 1=note / 0=null)

OR

  1. If ANY of the records in the group contain a note, do not include it in the query (i.e. - only query contracts where all records have notes = NULL)

I'm not certain this is possible, but would love some input / guidance.

Upvotes: 0

Views: 21

Answers (1)

GolezTrol
GolezTrol

Reputation: 116110

To check if any record contains a note, you can write a case that returns 1 or 0 depending on having a note or not. Maxing this value will return if there is at least one note. You could also sum it to get the number of notes.

SELECT 
  playerid,
  contractid,
  term,
  min(season) as yearone,
  exp_year,
  notes,
  max(case when notes is not null then 1 else 0 end) as HASNOTES,
  sum(case when notes is not null then 1 else 0 end) as NUMBEROFNOTES
FROM salaries
GROUP BY playerid, contractid
HAVING yearone <= 2014 AND exp_year > 2014

And obviously you can move that value to the HAVING clause, causing the query to not return those records that have a note:

HAVING
  max(case when notes is not null then 1 else 0 end) = 0

Upvotes: 1

Related Questions