Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2006
    Posts
    7

    Question Unanswered: Group By Clause Limitation

    Code is:
    select
    case when ItemCode is null then '-'
    else ItemCode
    End,
    case when sum(RecdQty) is null then '-'
    else sum(RecdQty)
    End
    from ItemMaster where ItemCode='V001' group by ItemCode


    Problem Statement:
    If query is not getting any records for above mentioned condition, then I want zero to be displayed if datatype is int (i.e. for sum(RecdQty) field) and '-' to be diplayed if datatype is varchar (i.e. for ItemCode field).
    In this situation, "ItemCode is null" and "sum(RecdQty) is null" conditions are not been utilised.
    Is this a limitation of case or group by clause?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    No, this is not a limitation of SQL at all, it is doing exactly what it is supposed to do. Please see my explanation from the last time you asked this question by clicking here. If that explanation isn't clear or sufficient, please continue the discussion in that thread instead of starting new threads.

    -PatP

  3. #3
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Consider this

    select count(*) from master..sysdatabases
    where 1=2

    will return 0. But

    select count(*) from master..sysdatabases
    where 1=2
    group by status

    return no records

    Now you want a specific ItemCode. There is no need for the group by

    select isnull(min(ItemCode),'-')
    ,isnull(sum(RecdQty),0)
    from ItemMaster
    where ItemCode='V001'
    Last edited by pdreyer; 10-14-06 at 18:59.

Posting Permissions

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