[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Doing two kinds of calculations in 1 SQL command

Posted on 2011-03-18
3
Medium Priority
?
226 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 1500 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
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.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

873 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