Group by + sum 1

[serializer]

I have this SQL:

select c.Name,cu.CompanyName,OfficialId,OrderOption, c.code + o.VATId as VATId, 
cast(round(TotalPriceIncVATSEK,0) AS decimal(10,0)) as Price,pp.Name as PaymentProcessor
from [order] o, customers cu, countries c,paymentprocessor pp
where paid = 1 and o.vatid !='' and vatadded = 0
AND tradedate >= '2012-01-01 00:00:00' AND tradedate < '2012-02-01 00:00:00'
AND c.code = cu.country
AND cu.customerid = o.customerid
AND pp.id = o.paymentprocessorid
AND c.code not in ('SE')

What I would like to do is to group rows on VATId and SUM those group rows on Price. Is it possible to do this? Thank you.

 

[IanWaterman]

Try
This groups as per original query

select c.Name,cu.CompanyName,OfficialId,OrderOption, c.code + o.VATId as VATId,pp.Name as PaymentProcessor, 
sum(cast(round(TotalPriceIncVATSEK,0) AS decimal(10,0))) as Price
from [order] o, customers cu, countries c,paymentprocessor pp
where paid = 1 and o.vatid !='' and vatadded = 0
AND tradedate >= '2012-01-01 00:00:00' AND tradedate < '2012-02-01 00:00:00'
AND c.code = cu.country
AND cu.customerid = o.customerid
AND pp.id = o.paymentprocessorid
AND c.code not in ('SE') 
Group by c.Name,cu.CompanyName,OfficialId,OrderOption, c.code + o.VATId , pp.Name

If you just want VATId group

select c.code + o.VATId as VATId, 
sum(cast(round(TotalPriceIncVATSEK,0) AS decimal(10,0))) as Price
from [order] o, customers cu, countries c,paymentprocessor pp
where paid = 1 and o.vatid !='' and vatadded = 0
AND tradedate >= '2012-01-01 00:00:00' AND tradedate < '2012-02-01 00:00:00'
AND c.code = cu.country
AND cu.customerid = o.customerid
AND pp.id = o.paymentprocessorid
AND c.code not in ('SE') 
Group by c.code + o.VATId

Ian

 

[serializer]

Thanks but what about the sum, in my case it returns like this:

GERMANY Company1 10977 CreditCard DExxxxxxxxx PayPal 437
GERMANY Company1 10978 CreditCard DExxxxxxxxx PayPal 437

But I want one row with a sum on the price like this:

GERMANY Company1 10977 CreditCard DExxxxxxxxx PayPal 874

Which OfficialId and PaymentProcessor does not matter, just as long as rows are summarized based on the unique VATId.

Thanks

 

[IanWaterman]

select c.Name,cu.CompanyName,Max(OfficialId) as ID,OrderOption, c.code + o.VATId as VATId,pp.Name as PaymentProcessor, 
sum(cast(round(TotalPriceIncVATSEK,0) AS decimal(10,0))) as Price
from [order] o, customers cu, countries c,paymentprocessor pp
where paid = 1 and o.vatid !='' and vatadded = 0
AND tradedate >= '2012-01-01 00:00:00' AND tradedate < '2012-02-01 00:00:00'
AND c.code = cu.country
AND cu.customerid = o.customerid
AND pp.id = o.paymentprocessorid
AND c.code not in ('SE') 
Group by c.Name,cu.CompanyName,OrderOption, c.code + o.VATId , pp.Name

Ian

 

[serializer]

Thank you - this works!