www.webdeveloper.com
Results 1 to 6 of 6

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

Hybrid View

  1. #1
    Join Date
    Jul 2007
    Posts
    245

    Exclamation 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.

  2. #2
    Join Date
    Feb 2012
    Location
    youTUBE
    Posts
    234
    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.

  3. #3
    Join Date
    Jul 2007
    Posts
    245
    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:

    Code:
    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;

  4. #4
    Join Date
    Jan 2006
    Location
    MN
    Posts
    440
    SELECT id FROM mytable WHERE val NOT NULL

  5. #5
    Join Date
    Feb 2012
    Location
    youTUBE
    Posts
    234
    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.
    Last edited by WyCnet; 02-22-2012 at 05:19 AM. Reason: add join effect.

  6. #6
    Join Date
    Oct 2009
    Posts
    658
    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

    Code:
    SELECT 
      id, 
      SUM(ISNULL(val, 0)) 
    FROM
      yourtable
    GROUP BY 
      id
    In MySQL change ISNULL to IFNULL

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles