asked on

Doing two kinds of calculations in 1 SQL command

Is there a way to incorporate two kinds of calculations in one SQL commands?
What I mean is this:
I have to print invoices based on an InvoiceDetails table.
Most clients have to pay TVA, some don’t. In my Clients table I have a Boolean field named BlnPayTax. This is set to 'True' for the clients paying TVA and to 'False' for all others.

Simplified my current SQL-command looks like this:

SELECT ClientId, SUM(Price) AS WithoutTVA, SUM(Price * 0.21) AS TVA
FROM InvoiceDetails
INNER JOIN Clients
ON InvoiceDetails.ClientId = Clients.ClientId
GROUP BY ClientId
This calculates the TVA for all Clients. Then In a second step I UPDATE the results, setting all TVA’s = 0 where Clients.BlnPayTax = ‘False’.

What would be nice is a command that calculates TVA in case BlnPayTVA is true en does not calculate TVA in all other cases.
Is this possible?

sgvill

try something like this:

SELECT       ClientId, CASE WHERE Clients.BlnPayTax = ‘True' THEN SUM(Price)  ELSE 0 END AS TVA
FROM          InvoiceDetails
INNER JOIN Clients
ON      InvoiceDetails.ClientId = Clients.ClientId
GROUP BY   ClientId

Open in new window

ASKER CERTIFIED SOLUTION

sgvill

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

NoraWil

ASKER

Thanks for your help.
I had to change the syntax a little bit to make it work
This works:
SELECT ClientId, SUM(Price) AS WithoutTVA, TVA =
CASE WHEN Clients.BlnPayTax= 'True'
THEN SUM(Price * 0.21)
ELSE 0
END
FROM InvoiceDetails
INNER JOIN Clients
ON InvoiceDetails.ClientId = Clients.ClientId
GROUP BY ClientId

I changed AS TVA to TVA =
and WHERE to WHEN

But your solution helped me to find the solution. Thanks.