Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Doing two kinds of calculations in 1 SQL command

Posted on 2011-03-18
3
221 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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

792 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