SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Enthusiast
    Join Date
    Mar 2007
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Making report from 4 tables details

    I'm trying to get the total of amount sold of the items in the store & the total of cash receipts. I used certain date .. it works find but i face several wrong data.


    i tried this query:
    PHP Code:

    SELECT DISTINCT
      orders
    .idorders_items.orderidorders_items.id AS ITEM_IDorders.useridusers.useridusers.fnameSUM((orders_items.price orders_items.discount) * (orders_items.quantity orders_items.quantity_store)) AS ITEM_PRICEorders_items.`date` AS ITEM_DATEorders_receipts.`date` AS RECEIPT_DATESUM(orders_receipts.amount) AS RECEIPT_AMOUNTorders_receipts.orderidorders_receipts.id AS REC_IDorders_receipts.check_date
    FROM
      orders
      INNER JOIN orders_items ON orders
    .id orders_items.orderid
      INNER JOIN orders_receipts ON orders
    .id orders_receipts.orderid
      INNER JOIN users ON orders
    .userid users.userid
    WHERE
      orders_items
    .`dateBETWEEN '2010-03-01' AND '2010-03-31' OR orders_receipts.`dateBETWEEN '1267390800' AND '1268168400'
    GROUP BY
      orders
    .id
    ORDER BY
      orders
    .id DESC 
    WRONG DATA:
    1- if there is no receipt paid the items sold will not show in the list!
    2- if there are many items sold, the receipts gets multiplied by the number of items!
    3- if there are many receipts, the items price gets multiplied by the number of receipts!

    Note: tables digram attached.
    Attached Images Attached Images

  2. #2
    SitePoint Enthusiast
    Join Date
    Mar 2007
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I was able to solve Problem No (1 & 2) by adding "Distinct" after the sum.

    QUERY Adjusted
    PHP Code:

    SELECT DISTINCT
      orders
    .idorders_items.orderidorders_items.id AS ITEM_IDorders.useridusers.useridusers.fnameSUM(Distinct((orders_items.price orders_items.discount) * (orders_items.quantity orders_items.quantity_store))) AS ITEM_PRICEorders_items.`date` AS ITEM_DATEorders_receipts.`date` AS RECEIPT_DATESUM(Distinct orders_receipts.amount) AS RECEIPT_AMOUNTorders_receipts.orderidorders_receipts.id AS REC_IDorders_receipts.check_date
    FROM
      orders
      INNER JOIN orders_items ON orders
    .id orders_items.orderid
      INNER JOIN orders_receipts ON orders
    .id orders_receipts.orderid
      INNER JOIN users ON orders
    .userid users.userid
    WHERE
      orders_items
    .`dateBETWEEN '2010-03-01' AND '2010-03-31' OR orders_receipts.`dateBETWEEN '1267390800' AND '1268168400'
    GROUP BY
      orders
    .id
    ORDER BY
      orders
    .id DESC 
    Now i want to show the items sold in the period selected even if the order not paid (no receipts add to the order)

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by ameenov View Post
    I was able to solve Problem No (1 & 2) by adding "Distinct" after the sum.
    that's a suboptimal solution

    the "multiples" problem that you had was due entirely to the fact that you were joining two one-to-many relationships in the query

    so if there were 13 of one relationship and 12 of the other, the join would generate 156 rows!!!

    then if you use SUM(DISTINCT ...) you do get the right result, however, this merely maskes the underlying inefficiency

    does that make sense?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    try this --
    Code:
    SELECT orders.id
         , users.userid
         , users.fname
         , oi.ITEM_PRICE
         , r.RECEIPT_AMOUNT
      FROM orders   
    INNER 
      JOIN users 
        ON users.userid = orders.userid
    INNER 
      JOIN ( SELECT orderid
                  , SUM((price - discount) * 
                        (quantity + quantity_store)) 
                     AS ITEM_PRICE
               FROM orders_items 
              WHERE `date` BETWEEN '2010-03-01' 
                               AND '2010-03-31' 
             GROUP
                 BY orderid ) AS oi
        ON oi.orderid = orders.id
    LEFT OUTER
      JOIN ( SELECT orderid
                  , SUM(amount) AS RECEIPT_AMOUNT
               FROM orders_receipts 
              WHERE `date` BETWEEN '1267390800' 
                               AND '1268168400' 
             GROUP
                 BY orderid ) AS r
        ON r.orderid = orders.id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Mar 2007
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thank you r937 for your help.
    it is showing now only the items which does not have receipts.
    which means i got what i want but i lost another one.

  6. #6
    SitePoint Enthusiast
    Join Date
    Mar 2007
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    let me try it again .. one minute i will get back to you

  7. #7
    SitePoint Enthusiast
    Join Date
    Mar 2007
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    it is working fine & the query runs fast but i noticed that when i go to an old order & add a new receipt the new receipt not showing in the query.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    that's due to the date restriction on the order_items table, which can be traced right back to your original query in post #1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Enthusiast
    Join Date
    Mar 2007
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes correct. but what i need is getting the order_items in the given date (done).
    OR orders_receipts in the given date.

    In that case i get only order_items AND orders_receipts in the given date.

    EXAMPLE:
    Case (1): I have a new items sold today & i got the payment (orders_receipts) today as well. When i search for the orders & the receipts for today date using the query given i get the sold items from table (order_items) & receipts payment from table (orders_receipts). WORKS GREAT

    Case (2): I have an old items sold from 01-01-2009 in the table (order_items). but i got the payment (orders_receipts) today only. When i search in the orders & the receipts i got for today date using the query given i get NOTHING While i have to see the new receipt payment form table (orders_receipts). NOT Working for me yet

    I should get the result in the two cases. while what i got it working now is only case 1.
    I hope you got my point.

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    ah yes, i see

    okay, try this --
    Code:
    SELECT orders.id
         , users.userid
         , users.fname
         , oi.ITEM_PRICE
         , oi.latest_order_time_date
         , r.RECEIPT_AMOUNT
         , r.latest_order_receipt_date
      FROM orders   
    INNER 
      JOIN users 
        ON users.userid = orders.userid
    INNER 
      JOIN ( SELECT orderid
                  , MAX(`date`) AS latest_order_time_date
                  , SUM((price - discount) * 
                        (quantity + quantity_store)) 
                     AS ITEM_PRICE
               FROM orders_items 
             GROUP
                 BY orderid ) AS oi
        ON oi.orderid = orders.id
    LEFT OUTER
      JOIN ( SELECT orderid
                  , MAX(`date`) AS latest_order_receipt_date
                  , SUM(amount) AS RECEIPT_AMOUNT
               FROM orders_receipts 
             GROUP
                 BY orderid ) AS r
        ON r.orderid = orders.id
     WHERE oi.latest_order_time_date 
           BETWEEN '2010-03-01' 
               AND '2010-03-31' 
        OR r.latest_order_receipt_date
           BETWEEN '1267390800' 
               AND '1268168400' 
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Enthusiast
    Join Date
    Mar 2007
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Great r937!
    That's what i need. but there are two things i noticed after i applied the new query.
    1- the query loading slow little bit.
    2- i get the wrong figure of "amount" in the table "orders_receipts" & the figure of "SUM((price - discount) * (quantity + quantity_store))" in the table "orders_items".

    How?!

    A- I get the total amount of orders_receipts of the order while i need only the total amount of orders_receipts in the specified date.

    Case: I have an order with payment from long time & payment for today. when i search for today payment i get the both payments (Old & today).

    B- I get the total amount of orders_items in the order while i need only the total amount of orders_items in the specified date.

    Case: I have an order with items sold from long time & the order updated today with new items. when i search for today items sold i get the both items sold (Old & today).

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    sorry, i can't fix those problems for you

    based on what i've shown you so far (couple of different versions of where to have the date criteria) you should be able to adjust the query yourself

    good luck
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Enthusiast
    Join Date
    Mar 2007
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    okay thank you r937, you have done so much help


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
  •