Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2012
    Posts
    7

    Red face Unanswered: sql complex query

    Hi All,



    I have a question to be solved. Its a tricked one for me.

    prod_id sale_date order_id quantity
    p1 1/10/2012 1 2
    p1 1/10/2012 2 5
    p2 1/10/2012 1 3
    p3 1/10/2012 2 6
    p2 1/11/2012 6 2
    p3 1/11/2012 6 3
    p1 1/12/2012 9 3
    p2 1/12/2012 9 6
    p3 1/12/2012 9 7

    Above shown is a table (name-sales_rec)

    Now if i want to fetch the PROD_ID WHICH IS SOLD IN ALL THE THREE DAYS i.e. 10th ,11th,12th jan. and also the quantity also.

    How can we find this?

    Could some one please help me.


    Thanks in advance.


    Preetpal kapoor

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT prod_id
         , SUM(quantity)
      FROM sales_rec
     WHERE sale_date IN ( '2012-01-10'
                        , '2012-01-11'
                        , '2012-01-12' )
    GROUP
        BY prod_id  
    HAVING COUNT(DISTINCT sale_date) = 3
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2012
    Posts
    7
    Thank you buddy.

Posting Permissions

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