Return NULL on a SELECT with GROUP BY when any grouped value is NULL

[Jazztronik]

How do I implement this?
I've checked that all the aggregator functions for GROUP BY ignore any NULL values, so I don't know anything else to get NULL when there is one or more NULL values in some grouped set of values from a LEFT JOIN.

Please this is urgent. Any suggestion would be greatly appreciated.

[WyCnet]

It sounds like you are doing random type searches through tabular data. If this is true some computing law may dictate reality as a two step process that has to be enacted. Your options include a tiny stored procedure, or scanning the columns in the result rows.

[Jazztronik]

Hi, and thanks for replying!

Well, I finally opted to get all the values without grouping them, and looping through them with php.

But there must be a way to do it entirely with SQL.

Someone in another forum pointed me in the right direction, but apparently his example only works when one and only one aggregated value is NULL:

select id,
       case max(    case when val is null then 1 end   )
         when 1 then null
         else sum( val )
       end total
from mytable
(...joins...)
group by id;

[Dasher]

SELECT id FROM mytable WHERE val NOT NULL

[WyCnet]

This is what Group does. I am selecting from real data.

select count(alias1.rdate),alias1.rdate,alias1.name,alias2.cause
from
sys1.alias1,sys1.alias2
where alias1.name=alias2.name
and alias1.name="No2Bots"
group by alias1.rdate

Group hides the count, but values in a group can be aggregated.

'33', '2011-02-24', 'No2bots', 'NNCx'
'88', '2011-04-28', 'No2Bots', 'NNCx'
'22', '2011-05-05', 'No2Bots', 'NNCx'
'44', '2011-06-25', 'No2Bots', 'NNCx'
'44', '2011-08-05', 'No2Bots', 'NNCx'
'22', '2011-08-08', 'No2Bots', 'NNCx'
'154', '2011-10-29', 'No2Bots', 'NNCx'
'44', '2011-11-05', 'No2Bots', 'NNCx'
'22', '2011-11-19', 'No2Bots', 'NNCx'

The joins are the key. The above example is a sort of join. The count shows how many times I tested that day, where each run produces eleven records of that type. Group without a count shows which days I tested. One remark is the importance of the primary search bias. If your primary search bases are spread over two tables, then you will get nulls mixed in with your union set. This entails strict exclusion and only join, no right, or left join!

p.s. In Business NULLS on a join mostly means incomplete information.

[ssystems]

Give us your schema and your entire current query. There are lots of ways to do this but without knowing what you have we are just working in assumptions. Example the query you have above could have been written in MSSQL like

SELECT 
  id, 
  SUM(ISNULL(val, 0)) 
FROM
  yourtable
GROUP BY 
  id

In MySQL change ISNULL to IFNULL