CODEWITHSUNDEEP
sqlsql-server
Chris
Chris

Reputation: 47

Complex Calculation - SQL Server

I'm currently working on calculating a larger set of data with a number of joins and the end result is a calculation across two tables. My current script looks like the following:

USE db1
Go

SELECT 
    customer, tb1.custid  
FROM 
    [dbo].[tb1]
LEFT OUTER JOIN 
    [dbo].[tb2] ON tb1.custid = tb2.custid
LEFT OUTER JOIN 
    [dbo].[tb3] ON tb2.custnumber = tb3.custnumber
LEFT OUTER JOIN 
    [dbo].[tb4] ON tb2.custid = tb4.custid
WHERE 
    tb1.custclass = 'non-person' 
    AND tb4.zip IN ('11111', '11112') 
GO

As you can see, it's not the cleanest, but it's working for gathering initial information. The reasoning for the number of joins is due to an incredibly odd table structure I did not create and the fact that the numerical data I need is only stored in tb3.

What I'm now trying to do is calculate the sum of 3 fields from tb3 that are all set as numeric fields and do an AND/OR comparison against a 4th field (also numeric). I know I can SUM them together, but I'm hoping for some input on three things:

  1. Where to place that SUM calculation in the query?
  2. Where to place and how to do the comparison of the SUM total against the 4th field?
  3. Is it possible to return the higher of the two values to a TOTAL column in the initial SELECT?

Thank you in advance.

Upvotes: 1

Views: 462

Answers (2)

sschimmel
sschimmel

Reputation: 196

You should be able to calculate the sum as a nested query:

SELECT (field1 + field2 + field3) AS fields_sum FROM tb3 (...)

Then in your main query you could do something like:

SELECT customer, tb1.custid, (CASE WHEN fields_sum > fourth_field THEN fields_sum ELSE fourth_field END) AS TOTAL (...)

Upvotes: 0

Jamiec
Jamiec

Reputation: 136104

Where to place that SUM calculation in the query?

If you want it output, you probably want to just add it to the SELECT

SELECT 
    customer, tb1.custid  
    (tb3.col1 + tb3.col2 + tb3.col3) as Sum
FROM 
   ...

Where to place and how to do the comparison of the SUM total against the 4th field?

You probably want to do this with a CASE statement, and this also answers your last question

Is it possible to return the higher of the two values to a TOTAL column in the initial SELECT?

SELECT 
    customer, tb1.custid  
    CASE WHEN (tb3.col1 + tb3.col2 + tb3.col3) > tb3.col4
         THEN (tb3.col1 + tb3.col2 + tb3.col3)
         ELSE tb3.col4
    END as Total
FROM 
   ...

Upvotes: 2

Related Questions