Page 1 of 3 123 LastLast
Results 1 to 15 of 44
  1. #1
    Join Date
    Sep 2006
    Posts
    7

    Exclamation Unanswered: Group by, creating a headache

    I am using SQL server 2000.
    While using query analyzer I am facing problem.
    If a query has group by clause and if that query is not fetching any record (i.e. query is returning nothing), then in this situation, I want zero to be displayed where datatype of field is integer and "-" if datatype of field is varchar.

    Please give me solution as soon as possible, a kind request.

    Facing problem for the below mentioned query:-

    select IsD.ItemCode,
    case
    when sum(IsD.IssuedQty) is null then 0
    else sum(IsD.IssuedQty)
    end as IssuedToday
    from Inv_IssueMaster IsM, Inv_IssueDetail IsD
    where IsM.IssueNo=IsD.IssueNo
    group by IsD.ItemCode


    In the above mentioned query, datatype of IssuedQty is int and for ItemCode it's varchar.
    Last edited by umeshm_patil; 10-13-06 at 01:03.

  2. #2
    Join Date
    Aug 2004
    Location
    Pune,India
    Posts
    94
    use the CASE operator for these columns where you need conditional values.

    Hope this helps.
    In GOD we believe. Everything else we Test!

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by umeshm_patil
    Please give me solution as soon as possible, a kind request.
    please show your query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Sep 2006
    Posts
    7

    Query also given.

    Quote Originally Posted by umeshm_patil
    I am using SQL server 2000.
    While using query analyzer, I am facing problem.
    If a query has group by clause and if that query is not fetching any record (i.e. query is returning nothing), then in this situation, I want zero to be displayed where datatype of field is integer and "-" if datatype of field is varchar.

    Please give me solution as soon as possible.
    Facing problem for the below mentioned query:-

    select IsD.ItemCode,
    case
    when sum(IsD.IssuedQty) is null then 0
    else sum(IsD.IssuedQty)
    end as IssuedToday
    from Inv_IssueMaster IsM, Inv_IssueDetail IsD
    where IsM.IssueNo=IsD.IssueNo
    group by IsD.ItemCode


    In the above mentioned query, datatype of IssuedQty is int and for ItemCode it's varchar.

  5. #5
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    Quote Originally Posted by umeshm_patil
    I want zero to be displayed where datatype of field is integer and "-" if datatype of field is varchar.
    I'm having trouble understanding your objective.

    You have 2 columns from 2 tables, the 2nd is 0 to many relationship: first Varchar, 2nd Int
    If the input is:

    A 10, 20, 25
    B null
    C 15

    You want results of

    A 55
    - 0
    C 15

    Is that correct? Why are you saying you want to check datatype

    Note: Are you aware of the "ISNULL" function? It's a poor-man's DECODE (Oracle's powerhouse fuction).

    I'm wondering if your question about datatype was just a confusion and this is all you want (I took the liberty of adding an outer join in case you have no detail records, you may still want to show a zero).

    Code:
    SELECT        IsD.ItemCode, 
                      sum(isnull(IsD.IssuedQty,0))  as IssuedToday
    FROM          Inv_IssueMaster IsM 
       LEFT OUTER JOIN Inv_IssueDetail IsD 
       ON             IsM.IssueNo=IsD.IssueNo 
    GROUP BY    IsD.ItemCode
    This would return:
    A 55
    B 0
    C 15

    If you really want the "-" to be output in the first column, then this would work:

    Code:
    SELECT 
       CASE WHEN IssuedToday = 0 then '-'
                ELSE SubQry.ItemCode end as ItemCode,
       IssuedToday
    FROM
    (
        SELECT        IsD.ItemCode, 
                          sum(isnull(IsD.IssuedQty,0))  as IssuedToday
        FROM          Inv_IssueMaster IsM 
           LEFT OUTER JOIN Inv_IssueDetail IsD 
           ON             IsM.IssueNo=IsD.IssueNo 
        GROUP BY    IsD.ItemCode
    ) SubQry
    
    This will return:
    A  55
    -   0
    C  15
    Last edited by vich; 10-13-06 at 01:52.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select IsD.ItemCode
         , coalesce(sum(IsD.IssuedQty),0) as IssuedToday
      from Inv_IssueMaster IsM
    inner
      join Inv_IssueDetail IsD
        on IsD.IssueNo = IsM.IssueNo
    group 
        by IsD.ItemCode
    the only way that this query "is not fetching any record (i.e. query is returning nothing)" is when there are no rows in the Inv_IssueMaster table, which doesn't seem likely
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    Quote Originally Posted by r937
    Code:
    select IsD.ItemCode
         , coalesce(sum(IsD.IssuedQty),0) as IssuedToday
      from Inv_IssueMaster IsM
    inner
      join Inv_IssueDetail IsD
        on IsD.IssueNo = IsM.IssueNo
    group 
        by IsD.ItemCode
    the only way that this query "is not fetching any record (i.e. query is returning nothing)" is when there are no rows in the Inv_IssueMaster table, which doesn't seem likely
    Interesting use of COALESCE instead of ISNULL. Somehow I sense that I'm unaware of some subtlity. Partell. (eagerly awaiting lesson)

    As for no records in the Master - that would normally be true in a typical table structure, but it is possible to set up such a relationship. It would be a strange and badly normalized design, but SQL would allow it.

    For example; let's say a school has the guy's SSN for student's that earn money from them and instead of linking the payment file into the student-ID, they have to link it to the SSN because the payment file doesn't have a "Student ID" column. They should create an intermediate XREF table, but they could also get lazy and just add the SSN to the STUDENT table. So this report would show "-" in the SSN column when there isn't one.

    Somehow; given the OP's inconclusive wording of the requirement I'm wondering if that's really what s/he ment though. I think his problem is solved and s/he may or may not return to clarify.
    Last edited by vich; 10-13-06 at 15:14.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by vich
    Interesting use of COALESCE instead of ISNULL. Somehow I sense that I'm unaware of some subtlity. Partell. (eagerly awaiting lesson)
    interesting? how about standard sql

    coalesce is standard, isnull isn't

    perhaps too subtle...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    Quote Originally Posted by r937
    interesting? how about standard sql

    coalesce is standard, isnull isn't

    perhaps too subtle...
    Thanks for the response.

    ISNULL is just a trimmer version (ie: smaller Object) of COALESCE so is likely a little faster. However it's propietary so not portable.

    So; sounds like a combination of habit, your need for portability, and maybe a general distain for propietary deviations from ANSI. Not some performance or reliability (within SQL Server) trick.

    I'll have to rethink some of those things if I ever write something that has to be portable. For now, in a SQL Server only shop, I'll opt for using the trimmer version, considering it gets used so many times.

    I see your point and it's a good one. I'm sure I'll continue using ISNULL, and when using Oracle I'll use DECODE, but I'll better appreciate why a Sr. SQL Consultant might do otherwise.

    (Quite honestly; I had forgotten that ISNULL wasn't standard. The danger of being a single shop guy - need to get out more. Ergo my sudden appearence on this forum. )
    Last edited by vich; 10-13-06 at 18:02.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    COALESCE blows the socks off ISNULL when there are more than two terms in the list

    likely faster? you cannot say that without extensive benchmarking, can you

    and what's a "smaller Object" -- is that some kind of object-oriented thingy? in which context would you need to measure this object size?

    i wasn't aware that the compiled execution plan would actually be bigger for one function in a query as compared to another
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    Quote Originally Posted by r937
    COALESCE blows the socks off ISNULL when there are more than two terms in the list

    likely faster? you cannot say that without extensive benchmarking, can you

    and what's a "smaller Object" -- is that some kind of object-oriented thingy? in which context would you need to measure this object size?

    i wasn't aware that the compiled execution plan would actually be bigger for one function in a query as compared to another
    Yes, object oriented thingy. General overhead thing. Use the minimum.

    Extensive benchmarking - I'd rather just take an educated guess and say "probably".

    Why would MS make it if not to optimize things a little? If they just did it for readability, and they actually made it slower, well .... that's just very unlikely. If I said "probably blow's it's socks off performance wise", now that would be an irresponsible statement. I'd stand by my original statmenet and say that common sense (and many years of programming experience) suffice for a "probably".

    "more than two terms in the list", well, that would be a different capability, more in line what that extra code is ment to handle. I think "blow socks off" is a misnomer. It's more binary than that, like SELECT vs. SET, since ISNULL does not accept multiple terms.

    Anyway; I didn't mean to start a shouting match. I acknowledge that you're probably far senior to myself in such matters and was ernestly looking for an insight. Your point is well taken and I believe it's a valid one.

    Cheers
    Last edited by vich; 10-13-06 at 18:01.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by vich
    Why would MS make it if not to optimize things a little?
    oh! oh! i know this one! to be compatible with sql standards, maybe?

    but thanks for the followup, and rest assured, i wasn't shouting

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    Quote Originally Posted by r937
    oh! oh! i know this one! to be compatible with sql standards, maybe?

    but thanks for the followup, and rest assured, i wasn't shouting

    See, now I'm curious about the overhead.

    Indeed, why would MS deviate from ANSI standard on this?

    My general project management experience tells me someone made a hit-list of "how to optimize, how to simplify". However; was it part of a marketing conspiracy to prevent SQL Server shops from migrating out? haha.

    If the former, then the gain would have to be substantial to justify it - or at least very easy to implement with a modest gain.

    Guess someone with an IN to the MS development team, or a very in depth book on SQL Server "improvements" would have to answer that one. Or like you say, develop a benchmark. Honestly; I'm not that curious.
    Last edited by vich; 10-13-06 at 18:04.

  14. #14
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Another deviation from ANSI SQL is the GROUP BY ALL (see BOL). Works OK if all you need to deal with is SQL Server.

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by vich
    "more than two terms in the list", well, that would be a different capability, more in line what that extra code is ment to handle. I think "blow socks off" is a misnomer.
    i should have been a bit more explicit

    COALESCE is easier to write, to understand, and to maintain ( = "blows the socks off") than a series of nested ISNULLs, when what you need to do is select the first non-null value in a series of values
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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