CODEWITHSUNDEEP
sqlsql-server
user3127554
user3127554

Reputation: 577

Select all column values as total and multiply it

This is my DB structure:

Invoice

InvoiceLine

These are my rows: 

Invoice
ID  Company
1   XYZ
2   ZYX

InvoiceLine
LineID  InvoiceID   quantity    price
1       1           1           10
2       1           5           10

3       2           1           20

What I'm trying to generate is to select my total invoice price dynamically:

IDEAL RESULT 

1 XYZ 60
2 ZYX 20

I use the following query for this: 

select ID, cil.quantity * cil.unitPrice as invoiceTotal from Invoice ci
join InvoiceLine as cil on ci.invoiceID = cil.invoiceID

Problem is that this query returns 2 rows for the first invoice.

Why is this and what how could I select ALL values of my invoice lines?

Upvotes: 0

Views: 31

Answers (2)

Usman Asif
Usman Asif

Reputation: 320

    select Invoice.id,Invoice.Company sum(InvoiceLine.quantity * InvoiceLine.unitPrice) as TotalSum
    from Invoice join
         InvoiceLine cil
         on Invoice.invoiceID = InvoiceLine.invoiceID 
    group by Invoice.id,Invoice.Company

order by Invoice.Company ;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269493

You want group by:

select ci.id, sum(cil.quantity * cil.unitPrice) as invoiceTotal
from Invoice ci join
     InvoiceLine cil
     on ci.invoiceID = cil.invoiceID 
group by ci.id;

However, you don't even need the join:

select cil.id, sum(cil.quantity * cil.unitPrice) as invoiceTotal
from InvoiceLine cil
group by cil.id;

Upvotes: 1

Related Questions