Mysql How To Assign A Null Value To Non Matching Columns When Using Group By

I have the following MovieTheaterTbl table: Name Location Date TicketRevenue SnackRevenue BeverageRevenue AMC Alpine St. 8/14 100 80

Solution 1:

You can use union adn having .. having count(*) > 1 insert null else insert the location

insertinto  SummaryTbl (Name,Location,Date,TicketRevenue,SnackRevenue,BeverageRevenue)
  select Name, NULL,Date,sum(TicketRevenue),sum(SnackRevenue), sum(BeverageRevenue)
  from MovieTheaterTbl
  groupby Name
  HAVINGCOUNT(*) >1andcount(location) >1UNIONselect Name, Location,Date,sum(TicketRevenue),sum(SnackRevenue), sum(BeverageRevenue)
  from MovieTheaterTbl
  groupby Name
  HAVINGCOUNT(*) =1ANDcount(location) =1

Solution 2:

Try this:

insertinto  SummaryTbl (Name,Location,Date,TicketRevenue,SnackRevenue,BeverageRevenue)
select Name, if(count(distinct Location)=1, Location, NULL), Date,sum(TicketRevenue),sum(SnackRevenue), sum(BeverageRevenue)
from MovieTheaterTbl
groupby Name