1

I am struggling to get my head around how to do this so please bare with me while I explain.

We have an order system which records orders, order lines, items, users etc. What I am interested in in this instance are the orders and order lines.

At the moment, we produce reports with sales figures, showing sales, returns etc, but it is slow and inefficient. This is because we are querying individual tables using PHP/MS SQL Server, looping through arrays and then bringing the data together.

I have been tasked to try and make this more efficient, and so I have tried to produce one query with joined, aliased, sub-query tables. These work fine and join together, but I am getting the wrong values due to the way I am grouping tables.

Note, I am unable to change any schema and I am working with an existing system.

This explanation may seem quite long-winded, and I probably will lose you with what I am trying to ask, but please stay with me.

Anyway, here is a diagram of the schema, sample data, and the result of my query at present:

Schema

SELECT 
`ol`.`storeID`,
SUM(DISTINCT `o`.`grossValue`) AS 'Total',
SUM(DISTINCT `o`.`paymentValue`) AS 'paymentTotal'
FROM `orders` AS `o`
LEFT JOIN `orderLines` AS `ol` ON `o`.`orderID` = `ol`.`orderID`
WHERE (`o`.`orderDate` BETWEEN '2015-07-07 00:00:00' AND '2015-07-07 23:59:59')
GROUP BY `ol`.`storeID`

I have the produced the above quickly in MySQL as I am at home at the moment, and I imagine the solution would be similar for SQL server, but please correct me if I'm wrong, or perhaps provide solutions for both, which would be great!

Basically, the orders have many lines to them. The orders record the total value of the lines, and how much has been paid so far. Each order line is a reference to a stock item. Each line records which store it is recorded to, as different items could be sourced from different stores. We want to produce reports that show how much each store makes in turnover.

As you can see in the result of my query store 06 is showing 4.99, however this should be 9.98.

What is happening is the netValue is being made distinct. I DON'T want this. I DON'T want duplicates of order <=> orderlines, but I DO want duplicates of the netValue as these are individual orders.

Have I confused you yet?

Is there a way to still group by the storeID and sum the netValue, by perhaps grouping by the orderID, or orderLineID?

I have tried all types of combinations, and gotten many errors which I don't remember now, but mostly about aggregate columns and not being able to group etc.

All suggestions and help welcome :)

Create table code:

CREATE TABLE `orderLines` (
  `orderLineID` int(10) NOT NULL AUTO_INCREMENT,
  `orderID` int(10) NOT NULL,
  `itemCode` varchar(30) NOT NULL,
  `netValue` decimal(10,2) NOT NULL,
  `taxValue` decimal(10,2) NOT NULL,
  `storeID` varchar(2) NOT NULL,
  PRIMARY KEY (`orderLineID`)
);



CREATE TABLE `orders` (
  `orderID` int(10) NOT NULL AUTO_INCREMENT,
  `grossValue` decimal(10,2) NOT NULL,
  `paymentValue` decimal(10,2) NOT NULL,
  `typeID` int(10) NOT NULL,
  `orderDate` datetime NOT NULL,
  PRIMARY KEY (`orderID`)
);

Expected Result:

+---------+-------+--------------+
| storeID | Total | paymentTotal |
+---------+-------+--------------+
| 02      | 10.00 |  8.00        |
| 06      | 9.98  |  9.98        |
| 07      | 3.00  |  3.00        |
+---------+-------+--------------+
  • Can you add the CREATE TABLE script and the output that you want in your question? – oNare Jul 7 '15 at 20:22
  • @oNare Question updated with those. – jren207 Jul 7 '15 at 21:10
  • 1
    You have StoreID in orderLines, rather than in orders, as if the same order could be shared by several stores. But if an order does indeed have various StoreID associated with its order lines, how can you select the same order total and the same payment total for each store? Or how would you divide the totals among the stores that come from the same order? Something is missing. – Andriy M Jul 8 '15 at 11:19
2

If it is safe to assume that a single order can have only one distinct StoreID, you could resolve your issue by generating the set of distinct OrderID, StoreID pairs from orderLines and join that set instead of the table itself. That way you will not need to use DISTINCT with aggregation:

SELECT 
  `ol`.`storeID`,
  SUM(`o`.`grossValue`) AS 'Total',
  SUM(`o`.`paymentValue`) AS 'paymentTotal'
FROM
  `orders` AS `o`
LEFT JOIN
  (SELECT DISTINCT `OrderID`, `StoreID` FROM `orderLines`) AS `ol`
  ON `o`.`orderID` = `ol`.`orderID`
WHERE
  (`o`.`orderDate` BETWEEN '2015-07-07 00:00:00' AND '2015-07-07 23:59:59')
GROUP
  BY `ol`.`storeID`
;

And, as has already been mentioned, you should probably use INNER JOIN instead of the LEFT JOIN, unless you have orders that do not have order lines but for some reason do have a gross value and a payment (a little strange, but may be there is a reason for that), and you want them included in the results. (They will be represented as a single row with a null Store ID.)

2

Remove the DISTINCT. But... Then you will probably find the results are too big. Here's why:

Think of gathering all the data for all the Orders + OrderLines -- one row for each combination. Notice how that will include o.grossValue is multiple times.

What you need to do is to the o. summations before JOINing to ol.

But now I am wondering about something. Your schema allows for one order to be fulfilled from multiple stores. However, since your data does not have an example of such, I wonder which is true:

  • An order can come from only one store. In this case moving the storeID to orders would be 'right' and make the query much simpler.
  • An order can come from multiple stores. In this case, please augment the data set so that a proposed solution can be checked.

I don't think you need 'LEFT'.

0

This is a SQL Server answer, hopefully similar in mySQL:

DECLARE @orderLines TABLE (
  orderLineID INT,
  orderID INT,
  itemCode VARCHAR(30),
  netValue DECIMAL(10,2),
  taxValue DECIMAL(10,2),
  storeID VARCHAR(2)
);
DECLARE @orders TABLE (
  orderID INT,
  grossValue DECIMAL(10,2),
  paymentValue DECIMAL(10,2),
  typeID INT,
  orderDate DATETIME
);

INSERT INTO @orderLines VALUES
(1, 1, 'BK001',    4.99, 0.00, '06'),
(2, 2, 'YX002',    2.00, 0.00, '06'),
(3, 2, 'CC5683',   2.99, 0.00, '06'),
(4, 3, 'LL007',    5.00, 0.00, '02'),
(5, 3, 'LL007',    5.00, 0.00, '02'),
(6, 4, 'MN476756', 3.00, 0.00, '07'),
(7, 5, 'LX/34',    0.33, 0.00, '12'),
(8, 5, 'LX/34',    0.33, 0.00, '12'),
(9, 5, 'LX/34',    0.33, 0.00, '12');

INSERT INTO @orders VALUES
(1, 4.99,  4.99, 0, '2015-07-07 14:09:00'),
(2, 4.99,  4.99, 0, '2015-07-07 14:32:19'),
(3, 10.00, 8.00, 0, '2015-07-07 15:13:07'),
(4, 3.00,  3.00, 0, '2015-07-07 16:22:10'),
(5, 0.99,  0.99, 0, '2015-07-08 09:03:00');

WITH orderLinesCTE AS
(
  SELECT storeID, orderID, sum(netValue) as netValue
  FROM @orderLines
  GROUP BY storeID, orderID
)
SELECT 
ol.storeID,
SUM(o.grossValue) AS 'Total',
SUM(o.paymentValue) AS 'paymentTotal'
FROM @orders AS o
LEFT JOIN orderLinesCTE AS ol ON o.orderID = ol.orderID
WHERE (o.orderDate BETWEEN '2015-07-07 00:00:00' AND '2015-07-07 23:59:59')
GROUP BY ol.storeID;
0

Assuming that you want to SUM the netValues of each storeID but a paymenValue distinct:

Query:

SELECT storeID,SUM(Total) AS Total,SUM(paymentTotal) AS paymentTotal FROM(
    SELECT ol.storeID,SUM(ol.netValue) AS 'Total',0.00  AS 'paymentTotal'
    FROM orders AS o
    LEFT JOIN orderLines AS ol ON (o.orderID = ol.orderID)
    WHERE o.orderDate BETWEEN '2015-07-07 00:00:00' AND '2015-07-07 23:59:59'
    GROUP BY ol.storeID
    UNION ALL
    SELECT ol.storeID,0.00  AS 'Total',o.paymentValue AS 'paymentTotal'
    FROM orders AS o
    LEFT JOIN orderLines AS ol ON (o.orderID = ol.orderID)
    WHERE o.orderDate BETWEEN '2015-07-07 00:00:00' AND '2015-07-07 23:59:59'
    GROUP BY ol.storeID) AS Result
GROUP BY storeID;

Test:

 mysql> SELECT storeID,SUM(Total) AS Total,SUM(paymentTotal) AS paymentTotal FROM(
    -> SELECT ol.storeID,SUM(ol.netValue) AS 'Total',0.00  AS 'paymentTotal'
    -> FROM orders AS o
    -> LEFT JOIN orderLines AS ol ON (o.orderID = ol.orderID)
    -> WHERE o.orderDate BETWEEN '2015-07-07 00:00:00' AND '2015-07-07 23:59:59'
    ->     GROUP BY ol.storeID
    ->     UNION ALL
    -> SELECT ol.storeID,0.00  AS 'Total',o.paymentValue AS 'paymentTotal'
    -> FROM orders AS o
    -> LEFT JOIN orderLines AS ol ON (o.orderID = ol.orderID)
    -> WHERE o.orderDate BETWEEN '2015-07-07 00:00:00' AND '2015-07-07 23:59:59'
    -> GROUP BY ol.storeID) AS Result
    -> GROUP BY storeID;
+---------+-------+--------------+
| storeID | Total | paymentTotal |
+---------+-------+--------------+
| 2       | 10.00 |         8.00 |
| 6       |  9.98 |         4.99 |
| 7       |  3.00 |         3.00 |
+---------+-------+--------------+
3 rows in set (0.00 sec)

mysql> 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.