How to Join two tables with SUM and GROUP BY in SQL server



  • I have 2 tables

    1. product
    id designation
    1 coca
    2 Pepsi
    3 Fanta
    4 Seven
    5 Eight

    2)sub_product

    product_id name quantity
    1 sm 10
    1 lg 10
    1 xl 20
    2 1L 10
    2 2L 20
    2 5L 20
    3 Ty 10
    3 Sa 20
    4 ha 20
    4 kd 30

    what I wanna have is this: the designation from the product table and total quantity which represent the SUM of the quantity that have the same product_id

    designation total quantity
    Coca 40
    Pepsi 50
    Fanta 30
    Seven 50

    Notes : I use SQL server



  • This can be accomplished simply with a https://docs.microsoft.com/en-us/sql/relational-databases/performance/joins?view=sql-server-ver15 and https://docs.microsoft.com/en-us/sql/t-sql/queries/select-group-by-transact-sql?view=sql-server-ver15 clause like so:

    SELECT 
        p.designation, 
        SUM(ISNULL(sp.quantity, 0)) AS [total quantity]
    FROM product AS p
    LEFT JOIN sub_product AS sp
        ON p.id = sp.product_id
    GROUP BY p.id, p.designation
    

    Note the use of a LEFT OUTER JOIN (aka a LEFT JOIN) to ensure that even products without any sub_products are still returned (with a total quantity of 0). If it's guaranteed there'll always be at least 1 sub_product for every product then you can use an INNER JOIN instead.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2