-2

When I run this:

SELECT 
    R.RegionID AS Region, C.CountryName AS Country, 
    S.SegmentName AS Segment, SUM(SKPI.KPI) AS YearlySalesKPI,   
    ROUND(SUM(SOLI.SalePrice), 2) AS YearlySales
FROM 
    Country C
INNER JOIN 
    Region R ON C.CountryID = R.CountryID
INNER JOIN 
    Segment S ON R.SegmentID = S.SegmentID
INNER JOIN 
    SalesRegion SR ON R.RegionID = SR.RegionID
INNER JOIN 
    SalesPerson SP ON SR.SalesPersonID = SP.SalesPersonID
INNER JOIN 
    SalesKPI SKPI ON SP.SalesPersonID = SKPI.SalesPersonID
INNER JOIN 
    SalesOrder SO ON SR.SalesRegionID = SO.SalesRegionID
INNER JOIN 
    SalesOrderLineItem SOLI ON SO.SalesOrderID = SOLI.SalesOrderID
INNER JOIN 
    Product P ON SOLI.ProductID = P.ProductID
INNER JOIN 
    ProductCost PC ON PC.ProductID = P.ProductID 
                   AND PC.CountryId = C.CountryID
GROUP BY 
    R.RegionID, C.CountryName, S.SegmentName
ORDER BY 
    C.CountryName ASC, R.RegionID ASC;

The query takes over 1 minute to complete, and when it does it gives me results that are larger than the real values.

I believe the additional tables are adding additional rows - when it shouldn't. But I am unsure on how to stop this from happening. I think their might be something wrong with the relationships but I'm unsure.

I have tried all methods. Any help is appreciated.

This is my database:

Click here for the diagram

Results

Expected Result: Expected results

Expected Results: Expected results

The expected results are the above columns shown in one query output.

Testnominiee
  • 9
  • 3
  • 4
    Create a https://dbfiddle.uk/ with sample data that demonstrate the problem and update the post with the url and expected result – Lennart - Slava Ukraini Aug 14 '22 at 05:46
  • 3
    Debug questions require a [mre]. All in your post, not just at a link. (@Lennart) PS [Why should I not upload images of code/data/errors when asking?](https://meta.stackoverflow.com/q/285551/3404097) [Why are images of text, code and mathematical expressions discouraged?](https://meta.stackexchange.com/q/320052/266284). [ask] [Help] PS Give DDL not ERDs. Give all & only what is needed to ask. When you get a result you don't expect, pause your overall goal, chop to the 1st subexpression with unexpected result & say what you expected & why, justified by documentation. (Debugging fundamental.) – philipxy Aug 14 '22 at 06:00
  • 1
    Double check your join conditions. Are you missing one between `SalesPerson` and `SalesOrder`? – Squirrel Aug 14 '22 at 06:58
  • See [How do comment replies work?](https://meta.stackexchange.com/q/43019/266284) to learn to use `@x` to notify 1 non-sole non-poster commenter `x` per comment about that comment. Posters, sole commenters & followers of posts always get notified. Without `@` other commenters get no notification. PS [mre] PS No images/links. – philipxy Aug 14 '22 at 07:45
  • 2
    Please don't use images for code, data or errors. Use formatted text. – Dale K Aug 14 '22 at 07:56
  • 2
    This is the same problem as your previous question, you need to debug your joins and ensure they are returning the data you want – Dale K Aug 14 '22 at 08:00
  • 1
    Think through your joins. What does each do? Does it return a single row per row of the left side, or does it return multiple? If it returns multiple, how do you propose to reduce that? Either use another join condition, or pre-aggregate it with a nested `SELECT` or pre-filter it using `ROW_NUMBER` etc – Charlieface Aug 14 '22 at 10:31

1 Answers1

-3

I'm not sure if I understand your problem. Here are my thoughts:

  1. For faster run time
  • Consider creating index for your tables.
  1. Results that are larger than real values
  • If you are referring to YearlySalesKPI and YearlySales, duplicate rows might be the reason behind it, try running your query without sum first, check if there are repeating values, additional column joins will probably solve it.
thaNyt
  • 1