SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Enthusiast omid020's Avatar
    Join Date
    Feb 2008
    Posts
    66
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    need help to make a condition

    This is query :
    Code:
    SELECT f.matName
           ,f.matCode
           ,COALESCE(SUM(`INPUT`), 0) - COALESCE(SUM(`OUTPUT`), 0) AS `INVENTORY`
           ,f.unitPrice
           ,(COALESCE(SUM(`INPUT`), 0) - COALESCE(SUM(`OUTPUT`), 0))
            * (COALESCE(unitPrice, 0)) AS `RialiInventory`
    
    FROM (
    ---subquery---
    ) AS `f`
    GROUP BY f.matName
             ,f.matCode
    In above query if (INVENTORY) value be less than "0" , then (RialiInventory) will be a negative value but I need to return "0" value for those (RialiInventory) values that their (INVENTORY) values are less than "0" .

    Note : I don`t want miss any (INVENTORY) value .

    thanks in advance

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,274
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT matName
         , matCode
         , inventory
         , unitPrice
         , CASE WHEN inventory < 0
                THEN 0
                ELSE inventory * COALESCE(unitPrice, 0) 
             END   AS RialiInventory
      FROM ( SELECT matName
                  , matCode
                  , COALESCE(SUM(`input`), 0) - 
                    COALESCE(SUM(`output`), 0) AS inventory
                  , unitPrice
               FROM (
                    ---subquery---
                    ) AS f
             GROUP 
                 BY f.matName
                  , f.matCode
                  , f.unitPrice ) AS x
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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