CODEWITHSUNDEEP
sqlsql-server
boatman50
boatman50

Reputation: 13

SQL Server: join data but in single row

I have a table Invoice that looks similar to this:

order_id completed_at subtotal discount_amount handling_amount
100 07/01/2021 10.09 0 0
101 07/04/2021 200.30 0 0
102 07/04/2021 54.10 0 0
103 07/06/2021 12.00 0 0

And I have another table InvoiceDetail:

order_id product_id qty amount
100 1234 1 1.09
100 Shipping1 1 4.30
100 SalesTax 1 .67
101 987 2 74.20
101 654 1 6.20
101 Shipping2 1 5.10
101 SalesTax 1 2.30
102 123 1 30.15
102 Shipping8 1 6.80
102 SalesTax 1 1.08
103 321 1 8.04
103 Shipping4 1 2.05
103 SalesTax 1 .70

I need to join the tables to look like this:

order_id completed_at subtotal shipping sales_tax discount_amount handling_amount
100 07/01/2021 10.09 4.30 .67 0 0
101 07/04/2021 200.30 5.10 2.30 0 0
102 07/04/2021 54.10 6.80 1.08 0 0
103 07/06/2021 12.00 2.05 .70 0 0

I have tried a few different things but every time it ends up with multiple rows for each order_id.

SELECT 
    order_id,
    completed_at,
    subtotal,
    CASE 
        WHEN t2.product_id LIKE '%Shipping%'
            THEN t2.amount 
    END AS shipping,
    CASE 
        WHEN t2.product_id = 'SalesTax'
            THEN t2.amount 
    END AS sales_tax,
    discount_amount,
    handling_amount
FROM 
    Invoice t1
INNER JOIN 
    InvoiceDetail t2 ON t1.order_id = t2.InvoiceDetail
WHERE 
    order_id BETWEEN '100' AND '103'

The above will output the information but in multiple rows for each order_id. I need the order information combined with the shipping and salestax all in one row. How do I achieve what I'm after?

Upvotes: 1

Views: 1533

Answers (2)

GarethD
GarethD

Reputation: 69769

You were close, you just need to use SUM() around your fields from InvoiceDetail and then GROUP BY your fields from Invoice:

SELECT  i.order_id,
        i.completed_at,
        i.subtotal,
        SUM(CASE WHEN id.product_id LIKE '%Shipping%' THEN id.Amount END) AS shipping,
        SUM(CASE WHEN id.product_id = 'SalesTax' THEN id.Amount END) AS sales_tax,
        i.discount_amount,
        i.handling_amount
FROM    dbo.Invoice AS i
        INNER JOIN dbo.InvoiceDetail AS id
            ON id.order_id = i.order_id
WHERE   i.order_id BETWEEN 100 AND 103
GROUP BY i.order_id, i.completed_at, i.subtotal, i.discount_amount, i.handling_amount;

N.B I updated your join condition from t1.order_id = t2.InvoiceDetail as this looks like an error in the question, it is presumably order_id from both tables.

I also removed the quotes from around 100 and 103. If order_id is an integer then the quotes are not necessary, if order_id is not an integer then BETWEEN is probably not the right operator to use.

Unrelated to your question, but with SQL Server it is a good idea to get in the habit of always including the schema prefix(Except for the cases where you genuinely want to switch schemas based on the execution context). Another good habit is to use meaningful table aliases. If you are using t1, t2, t3 etc this quickly gets very messy when you have large queries, t4 gives me no information whatsoever about which table it might be, but if you use prefixes that relate to the table itself (e.g. i for invoice, id for invoice detail), then it is much clearer when reading the code which column comes from which table.

Upvotes: 1

The Impaler
The Impaler

Reputation: 48780

You can use conditional aggregation using CASE inside a SUM() aggregation function. For example:

select
  i.order_id,
  max(i.completed_at) as completed_at,
  max(i.subtotal) as subtotal,
  sum(case when d.product_id like 'Shipping%' then amount end) as shipping,
  sum(case when d.product_id = 'SalesTax' then amount end) as sales_tax,
  max(i.discount_amount) as discount_amount,
  max(i.handling_amount) as handling_amount
from invoice i
left join invoicedetail d on d.order_id = i.order_id
group by i.order_id

Upvotes: 0

Related Questions