#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2006
    Posts
    277
    Rep Power
    0

    IF count problem in NW


    i am working on NW (northwind db for mysql) like following



    how many LILAS which employeeID bigger than 4

    Code:
    SELECT 
    COUNT( if(EmployeeID>4,1,0) ),
    SUM( if(Freight >1200,1,0) )
    FROM `orders`
    WHERE CustomerID = "LILAS"
    its result is following (its wrong )

    what is correct query ?



  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,163
    Rep Power
    4274
    Originally Posted by zodehala
    how many LILAS which employeeID bigger than 4
    Code:
    SELECT COUNT(CASE WHEN EmployeeID > 4
                      THEN 'ok'
                      ELSE NULL END) AS emp_count
         , COUNT(CASE WHEN Freight > 1200
                      THEN 'ok'
                      ELSE NULL END) AS freight_count
      FROM orders
     WHERE CustomerID = 'LILAS'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2006
    Posts
    277
    Rep Power
    0
    thanx but

    count emloyeeid WHEN Freight > X

    not count emloyeeid WHEN emloyeeid > X
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,163
    Rep Power
    4274
    Originally Posted by zodehala
    thanx but

    count emloyeeid WHEN Freight > X

    not count emloyeeid WHEN emloyeeid > X
    please, make up your mind

    and i have given you a good example, surely you can figure it out from that?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo