SQL group by clause understanding (simple question / SQL server 2005)

Question

HI all, I'm having some difficulty understanding the rationale behind group by aggregation in sql server 2005.

I have the following query which works fine and returns one row for each contact.id and the 1st occurence of event

SELECT
contact.id
,MIN(eve.date_created)
FROM _contact contact WITH(nolock)
INNER JOIN table2 tb2 WITH (nolock) ON contact.id = tb2.id1
INNER JOIN _event eve WITH (nolock) ON tb2.id2 = eve.id
INNER JOIN _cashtable cash WITH (nolock) ON cash.contact_id = contact.id
GROUP BY contact.id

However what I'm looking for is to have the following query and still only pull out one row per contact.id

SELECT
contact.id
,MIN(eve.date_created)
,cash.id2    -- the cash linked to the first event
,eve.id      -- the first event linked to the contact    
FROM _contact contact with (nolock)
INNER JOIN _table2 tb2 WITH (nolock) ON contact.id = tb2.id1
INNER JOIN _event eve WITH (nolock) ON tb2.id2 = eve.id
INNER JOIN _cashtable cash WITH (nolock) ON cash.contact_id = contact.id
GROUP BY contact.id

I get the standard error message saying that I need to add in cash.id2 and eve.id into the group by clause, which returns results I don't want.

I know that there are the potential options of using rank() / partitioning or even including the

select(MIN(eve.date_created) 

in the FROM clause, but I am not sure which would be best to put this and am still quite confused as to why SQL requires everything to be included in the group by statement and so any advice would be great :)

Thanks in advance!

Answer 1

You can select the Min ID for both the event and cash, which should be the oldest of each item, assuming there aren't any odd variations to the insert and linking logic for the entries.

The other alternative is adding clauses to the joins to restrict it to the first or MIN ID, then you won't need the grouping.

Answer 2

You shouldn't need to add them into the group-by clause, but you do need to aggregate them somehow. The point of the group-by clause is that you want a grouping of statistics. That is, you want everything for that same contact id, not just the first record.

I believe if you do something like

SELECT Contact.Id,
  MIN(eve.date_created),
  MAX(eve.date_created), //this will get you the range of events
  SUM(cash.id2), //total cash for all events
  MIN(eve.Id), //First event id
  MAX(eve.Id) //Latest event id
FROM _contact Contact //Etc. etc.

your query will work for grouping. What it seems like you want, however, is more like:

[Select your columns]
FROM _contact Contact 
INNER JOIN _Table2 tb2 on contact.id = tb2.id1 
  and tb2.id1 = (Select MIN(id2) from tb2 where tb2.id1 = contact.id)
INNER JOIN _event eve on tb2.Id2 = eve.id
INNER JOIN _cashtable cahs on cash.contact_id = contact.id
  and cash.id = (select MIN(id) from cash where cash.contact_id = contact.id)

YMMV, and I your table structure will partially dictate how fast that runs (that is, you may want to look at some optimizations). Also, I did that completely from memory, so you may need to play with those INNER JOINs to get them to work. The point here is that you're not trying to grab the group, your trying to grab the first one for each contact.id.

Answer 3

I think you need a subquery joining the event table with an aggregation of itself returning only the min(event dates)

SELECT
contact.id,min_created,cash.id2    -- the cash linked to the first event
,eve.id      -- the first event linked to the contact    
FROM _contact contact 
INNER JOIN _table2 tb2  ON contact.id = tb2.id1
INNER JOIN _event eve  ON tb2.id2 = eve.id
inner join (select id, date_created as min_created from _event group by id) eve_min
on eve_min.id = eve.id and eve_min.min_created = eve.date_created 
INNER JOIN _cashtable cash  ON cash.contact_id = contact.id