I have a field named kcal (int) and I want to sum it's values for different conditions. If i could select the kcal fields first, it should look like this, but I just want the value of the sum, where the columns meet some conditions.
SELECT kcal AS k1, kcal AS k2, kcal AS k3, (k1 + k2 + k3) FROM table WHERE ...
CREATE TABLE [dbo].[Menu] (
[id_product] INT NOT NULL,
[product_name] NCHAR (50) NOT NULL,
[description] TEXT NULL,
[price] INT NOT NULL,
[kcal] INT NOT NULL,
[dish] INT NOT NULL,
PRIMARY KEY CLUSTERED ([id_product] ASC)
SELECT (SELECT product_name FROM Menu WHERE dish = 1) AS Dish1, (SELECT product_name FROM Menu WHERE dish = 2) AS Dish2, (SELECT product_name FROM Menu WHERE dish = 3) AS Dish3, SUM(kcal) AS Total_Kcal , SUM(price) as Total_Price FROM Menu WHERE dish IN (1, 2, 3) GROUP BY dish HAVING SUM(pret) < 50 AND SUM(kcal) < 2000
There may be a better way, but this sounds like a CROSS JOIN with a plethora of WHERE conditions to me:
SELECT m1.product_name ,m2.product_name ,m3.product_name ,(m1.kcal + m2.kcal + m3.kcal) AS CombinedKcal ,(m1.price + m2.price + m3.price) AS CombinedPrice FROM Menu AS m1 ,Menu AS m2 ,Menu AS m3 WHERE -- Ensure combinations include each dish only once. (m1.id_product <> m2.id_product AND m2.id_product <> m3.id_product AND m3.id_product <> m1.id_product) AND -- Ensure combinations include a dish 1, a dish 2 and a dish 3 (m1.dish = 1 AND m2.dish = 2 AND m3.dish = 3) AND -- Ensure Combined Calories is less than 2000 (m1.kcal + m2.kcal + m3.kcal) < 2000 AND -- Ensure Combined Price is less than 50 (m1.price + m2.price + m3.price) < 50