best way to query this - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

best way to query this

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2015-04-29 : 15:20:06

I have

select invoiceid,firstname,lastname,totalamount,shippingprice from invoices i left join customers c on c.id=i.customerid

now I want also get the total payments in the same query

this would be
select sum(amount) from invoicepayments where invoiceid=

how do I combine the this to query the total payments within the first query.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-04-29 : 15:34:07

select i.invoiceid,firstname,lastname,totalamount,shippingprice,sum(ip.amount)
from invoices i
join invoicepayments ip on i.invoiceid=ip.invoiceid
left join customers c on c.id=i.customerid
group by i.invoiceid,firstname,lastname,totalamount,shippingprice

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-04-29 : 18:02:01

[code]
select invoiceid,firstname,lastname,totalamount,shippingprice,
SUM(amount) OVER(PARTITION BY invoiceid) AS total_invoice_amount
from invoices i left join customers c on c.id=i.customerid

[/code]

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2015-05-01 : 03:01:58

select i.invoiceid,firstname,lastname,totalamount,shippingprice,sum(ip.amount)
from invoices i
join invoicepayments ip on i.invoiceid=ip.invoiceid
left join customers c on c.id=i.customerid
group by i.invoiceid,firstname,lastname,totalamount,shippingprice

this worked but I cna't put a where totalamount=sum(ip.amount)>10
how I can add this the where

(I want to be able to query all those that owe over a certain amount

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-05-01 : 12:24:04

select i.invoiceid,firstname,lastname,totalamount,shippingprice,sum(ip.amount)
from invoices i
join invoicepayments ip on i.invoiceid=ip.invoiceid
left join customers c on c.id=i.customerid
group by i.invoiceid,firstname,lastname,totalamount,shippingprice
having sum(ip.ammount)>10

?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources