I am trying to show only the highest selling product per work center, however, it keeps telling me that LIMIT 1 is the incorrect syntax. I cant seem to find an answer on here that has helped so I decided to ask the question. Here's my query.
SELECT WorkCenter.WorkCenterCode, Product.Name, SUM(CustomerOrderLine.Quantity*CustomerOrderLine.ActualPrice) AS 'Total Sales'
FROM WorkCenter
INNER JOIN Product ON WorkCenter.WorkCenterCode = Product.WorkCenterCode
INNER JOIN CustomerOrderLine ON Product.ProductID = CustomerOrderLine.ProductID
GROUP BY WorkCenter.WorkCenterCode, Product.Name
ORDER BY 'Total Sales' DESC
LIMIT 1
Here is your query cleaned up a bit:
SELECT wc.WorkCenterCode, p.Name, SUM(col.Quantity*col.ActualPrice) AS "Total Sales"
FROM WorkCenter wc INNER JOIN
Product p
ON wc.WorkCenterCode = p.WorkCenterCode INNER JOIN
CustomerOrderLine col
ON p.ProductID = col.ProductID
GROUP BY wc.WorkCenterCode, p.Name
ORDER BY "Total Sales" DESC
LIMIT 1
Note the important change from double quotes to single quotes. This is especially important for the order by
clause so the clause actually does something, rather than sorting by a constant. The addition of table aliases makes the query easier to read.
If you are using Visual Studio, you should use top
rather than limit
:
SELECT TOP 1 wc.WorkCenterCode, p.Name, SUM(col.Quantity*col.ActualPrice) AS "Total Sales"
FROM WorkCenter wc INNER JOIN
Product p
ON wc.WorkCenterCode = p.WorkCenterCode INNER JOIN
CustomerOrderLine col
ON p.ProductID = col.ProductID
GROUP BY wc.WorkCenterCode, p.Name
ORDER BY "Total Sales" DESC;
EDIT:
For one row per work center, use this as a subquery with row_number()
:
SELECT WorkCenterCode, Name, "Total Sales"
FROM (SELECT wc.WorkCenterCode, p.Name, SUM(col.Quantity*col.ActualPrice) AS "Total Sales",
row_number() over (partition by wc.WorkCenterCode order by SUM(col.Quantity*col.ActualPrice) desc) as seqnum
FROM WorkCenter wc INNER JOIN
Product p
ON wc.WorkCenterCode = p.WorkCenterCode INNER JOIN
CustomerOrderLine col
ON p.ProductID = col.ProductID
GROUP BY wc.WorkCenterCode, p.Name
) t
WHERE seqnum = 1
ORDER BY "Total Sales" DESC;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With