jwwceo Posted October 23, 2008 Share Posted October 23, 2008 Hello, I am trying to write a report query to find all the sales for all past months. I have the query almost working. I have the timestamp stuff working, but the problem is, there is an additional criteria I am trying to sort by, called PROVIDER. And the PROVIDER field is in another table I am joining to. However, this is a one-to-many relationship, where each orderid has many entries in the second table. This occurs when the person orders more than one item. So I have added a DISTINCT clause, SELECT DISTINCT xcart_order_details.orderid, FROM_UNIXTIME( xcart_orders.date, '%M %Y' ) AS yearmonth, xcart_orders.total FROM xcart_orders JOIN xcart_order_details ON xcart_orders.orderid = xcart_order_details.orderid WHERE xcart_order_details.provider = 'XXX' AND xcart_orders.status = 'C' GROUP BY xcart_order_details.orderid ] and this gets me the data you see below. orderid yearmonth total 181560 July 2008 838.08 181789 July 2008 256.32 181795 July 2008 0.00 181796 July 2008 648.60 181912 August 2008 1666.75 181957 August 2008 1503.45 181998 August 2008 1104.18 182015 August 2008 667.25 182017 August 2008 1212.00 182018 August 2008 983.52 182019 August 2008 4774.50 182021 August 2008 666.00 182115 August 2008 3096.77 182252 September 2008 3031.50 182253 September 2008 780.00 182280 September 2008 1889.84 182300 September 2008 1362.50 182325 September 2008 4857.50 182387 September 2008 1468.50 182489 September 2008 988.14 184603 October 2008 730.34 This SUM of these orders should add to 32525.74 Yet, when I change the query to SUM that column, SUM(xcart_orders.total) as TOTAL I get this: orderid yearmonth TOTAL 181560 July 2008 838.08 181789 July 2008 256.32 181795 July 2008 0.00 181796 July 2008 1297.20 181912 August 2008 5000.25 181957 August 2008 1503.45 181998 August 2008 1104.18 182015 August 2008 667.25 182017 August 2008 1212.00 182018 August 2008 983.52 182019 August 2008 19098.00 182021 August 2008 666.00 182115 August 2008 12387.08 182252 September 2008 6063.00 182253 September 2008 780.00 182280 September 2008 5669.52 182300 September 2008 1362.50 182325 September 2008 4857.50 182387 September 2008 1468.50 182489 September 2008 988.14 184603 October 2008 730.34 Which is aggregating ALL THE MATCHES FROM THE JOIN ( still grouped by ORDER_ID), and not just the ones displaying in the query results. I have been playing with this for hours, and I cant seem to find a way to make it only ADD THE RESULTS which are shown in the query results. Its like I want to group them by two methods, first by ORDER_ID, and then by YEARMONTH, but passing multiple group by arguments doesnt work either. Best, James Quote Link to comment Share on other sites More sharing options...
jwwceo Posted October 23, 2008 Author Share Posted October 23, 2008 I solved this using a subquery. Thanks anyway! Quote Link to comment Share on other sites More sharing options...
fenway Posted October 23, 2008 Share Posted October 23, 2008 I solved this using a subquery. Thanks anyway! How about posting the solution for the rest of us. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.