Liviu Liviu - 4 years ago 100
SQL Question

How to sum the same field in SQL without selecting the field

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 ...


EDIT :I have this table

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)


);

And i want to display all the combinations possible of 3 product_name (each one with a different dish value(which can be 1, 2 or 3)), the total_kcal of each 3 combinations, the total price of each 3 combinations, with the condition that the sum of price <50 and the sum of kcal < 2000. For that, i wrote this:

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


It works just fine, with the problem that the sums are not corresponding.

Answer Source

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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download