0

I have a list of states with a count being attached to each one. I would like to combine the rows that share a similar state, so that there is only 1 row per state and the counts are either the number or null.

Query

select COALESCE(t1.State, t2.State, t3.State) AS 'State', t1.DMECount, t2.TransCount, t3.ProCount 
from #TestTable t1
FULL OUTER JOIN #TestTable2 t2 ON t1.State = t2.State
FULL OUTER JOIN #TestTable3 t3 ON t2.State = t3.State
ORDER BY 'State'

Current Result

State|DMECount|TransCount|ProCount
MA   |19      |NULL      |NULL
MA   |NULL    |NULL      |1
MD   |NULL    |NULL      |1
MD   |8       |NULL      |NULL
MI   |1450    |1         |259
MN   |21      |NULL      |NULL
MN   |NULL    |NULL      |2

Desired Result

State|DMECount|TransCount|ProCount
MA   |19      |NULL      |1
MD   |8       |NULL      |1
MI   |1450    |1         |259
MN   |21      |NULL      |2

From other solutions I've looked at, it seems like grouping by the state and taking the MAX of the others may do what I need, but I cannot group by a field that has COALESCE being used on it. The COALESCE is being used so that way if one of the tables has a state that the others don't, the state column uses that value instead of NULL.

Chris Albert
  • 2,462
  • 8
  • 27
  • 31
Devined
  • 186
  • 1
  • 9
  • 1
    GROUP BY the current query result. – jarlh Jan 10 '18 at 21:34
  • Would you provide the definition of `#TestTable1`, `#TestTable2` and `#TestTable3`? It would be very helpful if you could provide the sample data that when after populating those tables we can get the same result as you did with your query. – Emilio Lucas Ceroleni Jan 11 '18 at 03:13

1 Answers1

1

You simply need to include the COALSCE statement in your GROUP BY clause:

select COALESCE(t1.State, t2.State, t3.State) AS 'State', max(t1.DMECount), max(t2.TransCount), Max(t3.ProCount)
from #TestTable t1
FULL OUTER JOIN #TestTable2 t2 ON t1.State = t2.State
FULL OUTER JOIN #TestTable3 t3 ON t2.State = t3.State
GROUP BY COALESCE(t1.State, t2.State, t3.State)
ORDER BY COALESCE(t1.State, t2.State, t3.State)
fauxmosapien
  • 525
  • 3
  • 6