Consulting

Results 1 to 4 of 4

Thread: Need Help with SQL Query Syntax for Complex Join and Aggregation

  1. #1
    VBAX Regular
    Joined
    Jun 2024
    Location
    US
    Posts
    8
    Location

    Need Help with SQL Query Syntax for Complex Join and Aggregation

    Hello,


    I am currently working on a project where I need to retrieve specific data from multiple tables in my SQL Server database. I have three tables: Orders, OrderDetails, and Products. Orders table contains order information with columns OrderID and OrderDate. OrderDetails table contains details about each order item with columns OrderID, ProductID, and Quantity. Products table contains product details with columns ProductID and ProductName.

    What I need to achieve is to retrieve a summary report showing the total quantity of each product ordered within a specified date range; grouped by product name ; along with the total quantity ordered overall.

    I am trying to write a SQL query to accomplish this but have been running into syntax errors and difficulties with the aggregation.
    I have tried so far:


    HTML Code:
    SELECT 
        p.ProductName,
        SUM(od.Quantity) AS TotalQuantity,
        -- Additional aggregation and grouping logic
    
    FROM 
        Orders o
        INNER JOIN OrderDetails od ON o.OrderID = od.OrderID
        INNER JOIN Products p ON od.ProductID = p.ProductID
    
    WHERE 
        o.OrderDate BETWEEN '2024-01-01' AND '2024-06-30'
    GROUP BY 
        p.ProductName
    ORDER BY 
        TotalQuantity DESC;
    However, this query isn't yielding the correct results; and I'm struggling to properly aggregate the quantities and include the overall total in the same result set.

    Could someone please help me correct the syntax and suggest how to structure the query to achieve the desired output?





    Any help would be greatly appreciated.







    Thank you!
    gregbowerscpq
    Last edited by Aussiebear; 06-26-2024 at 09:52 PM.

  2. #2
    replace the Extra comma (,) after As TotalQuantity with Space and run it again.

  3. #3
    VBAX Mentor
    Joined
    Nov 2022
    Location
    The Great Land
    Posts
    373
    Location
    What does "-- Additional aggregation and grouping logic" mean - more calculated fields?

    "total quantity ordered overall" means total for all products in given range? You want this total shown on every record? Think have to build query for product aggregation, a query for total aggregation, then OUTER JOIN those queries. Each of those queries will need same filter criteria.

    You are building this query in SSMS? What are you using for GUI - Access? Consider building a report and doing Sorting & Grouping and aggregate calcs there.
    Last edited by June7; 06-26-2024 at 10:31 PM.
    How to attach file: Reading and Posting Messages (vbaexpress.com), click Go Advanced below post edit window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    VBAX Newbie
    Joined
    Jul 2024
    Posts
    4
    Location
    Your query is almost there, but to include the overall total quantity in the result set, you'll need to use a subquery or a CTE (Common Table Expression) to calculate the total quantity separately. Here’s an approach using a CTE to achieve your desired output:

    WITH ProductSummary AS (
        SELECT 
            p.ProductName,
            SUM(od.Quantity) AS TotalQuantity
        FROM 
            Orders o
            INNER JOIN OrderDetails od ON o.OrderID = od.OrderID
            INNER JOIN Products p ON od.ProductID = p.ProductID
        WHERE 
            o.OrderDate BETWEEN '2024-01-01' AND '2024-06-30'
        GROUP BY 
            p.ProductName
    )
    SELECT 
        ps.ProductName,
        ps.TotalQuantity,
        (SELECT SUM(Quantity) 
         FROM Orders o
         INNER JOIN OrderDetails od ON o.OrderID = od.OrderID
         WHERE o.OrderDate BETWEEN '2024-01-01' AND '2024-06-30') AS OverallTotalQuantity
    FROM 
        ProductSummary ps
    ORDER BY 
        ps.TotalQuantity DESC;
    This query uses a CTE to calculate the total quantity of each product ordered within the specified date range. In the final SELECT statement, it retrieves the product name and total quantity from the CTE and adds a subquery to calculate the overall total quantity. This should give you the summary report you need.
    Last edited by Aussiebear; 07-04-2024 at 03:13 AM.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •