[Solved] SQL join and multiply

EverSQL Database Performance Knowledge Base

SQL join and multiply

I'm pretty new to SQL, and all of my background is in procedural languages. I'm having quite a bit of difficulty, and until now have been able to research the answers to all of my questions.

I have two tables, structured like so:

dbo.SERVICE - A list of all service orders/maintenance calls ever

SERVICE_ORDER_NO | PART_NO | QUANTITY | PART_DESC | CREATED_DT | CHANGED_DT

dbo.PART - A list of all parts in the machine being serviced

PART_NO | COST

I would like to get a list of the parts used in a given time frame, including the total quantity and total cost, and sort by quantity. So far I have been able to get the parts used and the quantity appropriately, using the following query (dates have been hardcoded for simplicity):

SELECT SERV.PART_NO, SUM(SERV.QUANTITY) AS QUANTITY
FROM dbo.SERVICE SERV
WHERE SERV.CREATED_DT >= '01/01/2012'  AND SERV.CREATED_DT < '02/01/2012'
GROUP BY SERV.PART_NO
ORDER BY QUANTITY DESC

I'm having trouble getting the total cost though. I tried this:

SELECT SERV.PART_NO, SUM(SERV.QUANTITY) AS QUANTITY, SUM(SERV.QUANTITY * PART.COST) AS COST
FROM dbo.SERVICE SERV
INNER JOIN dbo.PART PART
ON PART.PART_NO = SERV.PART_NO
WHERE SERV.CREATED_DT >= '01/01/2012'  AND SERV.CREATED_DT < '02/01/2012'
GROUP BY SERV.PART_NO
ORDER BY QUANTITY DESC

However, the numbers are wrong (quantities are much too high), and I get about 1600 rows when my previous query gave me 1900. I'm assuming there's something wrong with my JOIN statement, but I don't know what it is. Can anyone give me a working query, or point me to a good reference for my problem?

Any direction to good general SQL help repositories would be great too... I haven't really found any one place that is both comprehensive and easily understood.

PS - Is that fact that I call SUM() twice in that query going to slow my code down? I don't know how SQL works... is it smart enough to take the results from the QUANTITY column and multiply them, or no? If not, what is the syntax to run it more efficiently?

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
ALTER TABLE `SERVICE` ADD INDEX `service_idx_created_dt` (`CREATED_DT`);
ALTER TABLE `SERVICE` ADD INDEX `service_idx_part_no` (`PART_NO`);
The optimized query:
SELECT
        SERV.PART_NO,
        SUM(SERV.QUANTITY) AS QUANTITY 
    FROM
        dbo.SERVICE SERV 
    WHERE
        SERV.CREATED_DT >= '01/01/2012' 
        AND SERV.CREATED_DT < '02/01/2012' 
    GROUP BY
        SERV.PART_NO 
    ORDER BY
        QUANTITY DESC

Related Articles



* original question posted on StackOverflow here.