CODEWITHSUNDEEP
sqlsql-server
David
David

Reputation: 1

Query to pick preferred available row in SQL

I use SQL server. I currently have a table which contains several items, some of them have a price and some not. However some of them have several prices columns: “Commercial price”, “Current price”, “New price”

It is possible that for some of the items not all of the 3 pricing columns above exist, and some only have “Current price” and the rest is NULL.

So I need the query to check which one of the prices columns exist/isn’t null, by the following “priority” New price > Current price > Commercial price (That means that if “New price” doesn’t exist or is NUll, the query will pick the price from “Current price”)

And in the end to add a row/columns which is named “Total” and calculates all the prices together.

Thanks a lot!

Upvotes: 0

Views: 99

Answers (2)

Vijay
Vijay

Reputation: 122

SELECT ID, SUM(ISNULL([New Price], 0) + ISNULL([Current price], 0) + ISNULL(Commercial price], 0) AS TotalPrice FROM YOURTABLE
GROUP BY ID
UNION 
SELECT COUNT(ID), SUM(ISNULL([New Price], 0) + ISNULL([Current price], 0) + ISNULL(Commercial price], 0) AS TotalPrice FROM YOURTABLE

Upvotes: 0

Cedersved
Cedersved

Reputation: 1025

SELECT COALESCE([New price],[Current price],[Commercial price]) as Price

For the last part you could add a UNION with another select (possibly grouped by product or whatever), something like:

SELECT SUM(ISNULL([New Price], 0) + ISNULL([Current price], 0) + ISNULL(Commercial price], 0) AS Total

Upvotes: 1

Related Questions