Solved

Doing two kinds of calculations in 1 SQL command

Posted on 2011-03-18
3
220 Views
Last Modified: 2012-05-11
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?
0
Comment
Question by:NoraWil
  • 2
3 Comments
 
LVL 4

Expert Comment

by:sgvill
ID: 35165176
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

0
 
LVL 4

Accepted Solution

by:
sgvill earned 500 total points
ID: 35165197
oh, that didn't show up too well... try this

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

Author Closing Comment

by:NoraWil
ID: 35166404
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.
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question