Return NULL on a SELECT with GROUP BY when any grouped value is NULL
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.
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.
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:
case max( case when val is null then 1 end )
when 1 then null
else sum( val )
group by id;
SELECT id FROM mytable WHERE val NOT NULL
This is what Group does. I am selecting from real data.
Group hides the count, but values in a group can be aggregated.
group by alias1.rdate
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!
'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'
p.s. In Business NULLS on a join mostly means incomplete information.
Last edited by WyCnet; 02-22-2012 at 05:19 AM.
Reason: add join effect.
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
In MySQL change ISNULL to IFNULL
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
Tags for this Thread