Solved

Doing two kinds of calculations in 1 SQL command

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

947 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now