Write SQL query with division, total and %

Question

I'm writing test queries in MS SQL Server to test reports. Can't figure out how to calculate following:

Ingredient_Cost_Paid / Total Ingredient_Cost_Paid * 100 as 'Ingredient Cost Allow as % of Total'

This is Ingredient cost allowable as a percentage of the total ingredient cost allowable.

P.S. I'm new to SQL, so would appreciate explanations as well, so I learn for the future. Thanks Also I'm not sure I correctly understand difference between Total and SUM.

Thanks everyone

Answer 1

The single quote (') is used as a delimiter for textual values. If you use the AS keyword to specify a (column) alias, you need to use square brackets ([]) if it includes spaces and/or special characters:

Ingredient_Cost_Paid / Total_Ingredient_Cost_Paid * 100 as [Ingredient Cost Allow as % of Total]

Is that what you are looking for?

Edit: I noticed that it also works with single quotes! I didn't know that! But honestly, I would not use it. I'm not sure if it's officially considered to be valid.

Regarding the difference between "Total" and SUM, I would need to understand what you mean with "Total", since that is not something that SQL understands. You could probably use the SUM aggregate function to calculate a total. An aggregate function calculates a value based on a certain column/expression in groups of rows (or in the entire table as a whole single group). So you probably need to provide (much) more information in your question to get effective help with that.

Edit:

I would like to elaborate a little on this SQL issue for you. My apologies in advance for this rather lengthy post. ;)

For example, assume that all query logic described here applies to a table called Recipe_Ingredients, which contains rows with information about ingredients for various recipes (identified by the column Recipe_ID) and the price of the recipe ingredient (in a column called Ingredient_Cost_Paid).

The (simplified) table definition would look something like this:

CREATE TABLE Recipe_Ingredients (
  Recipe_ID INT NOT NULL,
  Ingredient_Cost_Paid NUMERIC NOT NULL
);

For testing purposes, I created this table in a test database and populated it with the following query:

INSERT INTO Recipe_Ingredients
VALUES
  (12, 4.65),
  (12, 0.40),
  (12, 9.98),
  (27, 5.35),
  (27, 12.50),
  (27, 1.09),
  (27, 3.00),
  (65, 2.35),
  (65, 0.99);

You could select all rows from the table to view all data in the table:

SELECT
  Recipe_ID,
  Ingredient_Cost_Paid
FROM
  Recipe_Ingredients;

This would yield the following results:

Recipe_ID  Ingredient_Cost_Paid
---------  --------------------
12         4.65
12         0.40
12         9.98
27         5.35
27         12.50
27         1.09
27         3.00
65         2.35
65         0.99

You could group the rows based on corresponding Recipe_ID values. Like this:

SELECT
  Recipe_ID
FROM
  Recipe_Ingredients
GROUP BY
  Recipe_ID;

This will yield the following result:

Recipe_ID
---------
12
27
65

Not very spectacular, I agree. But you could ask the query to calculate values based on those groups as well. That's where aggregate functions like COUNT and SUM come into play:

SELECT
  Recipe_ID,
  COUNT(Recipe_ID) AS Number_Of_Ingredients,
  SUM(Ingredient_Cost_Paid) AS Total_Ingredient_Cost_Paid
FROM
  Recipe_Ingredients
GROUP BY
  Recipe_ID;

This will yield the following result:

Recipe_ID  Number_Of_Ingredients  Total_Ingredient_Cost_Paid
---------  ---------------------  --------------------------
12         3                      15.03
27         4                      21.94
65         2                      3.34

Introducing your percentage column is somewhat tricky. The calculation has to be performed on a rowset (a table or a query result) and cannot be expressed directly in a SUM.

You could specify the previous query as a subquery in the FROM-clause of another query (this is called a table expression) and join it with table Recipe_Ingredients. That way you combine the group data back with the detail data.

I will drop the Number_Of_Ingredients column from now on. It was just an example for the COUNT function, but you do not need it for your issue at hand.

SELECT
  Recipe_Ingredients.Recipe_ID,
  Recipe_Ingredients.Ingredient_Cost_Paid,
  Subquery.Total_Ingredient_Cost_Paid
FROM
  Recipe_Ingredients
  INNER JOIN (
    SELECT
      Recipe_ID,
      SUM(Ingredient_Cost_Paid) AS Total_Ingredient_Cost_Paid
    FROM
      Recipe_Ingredients
    GROUP BY
      Recipe_ID
  ) AS Subquery ON Subquery.Recipe_ID = Recipe_Ingredients.Recipe_ID;

This will yield the following results:

Recipe_ID  Ingredient_Cost_Paid  Total_Ingredient_Cost_Paid
---------  --------------------  --------------------------
12         4.65                  15.03
12         0.40                  15.03
12         9.98                  15.03
27         5.35                  21.94
27         12.50                 21.94
27         1.09                  21.94
27         3.00                  21.94
65         2.35                  3.34
65         0.99                  3.34

With this, it is pretty easy to add your calculation for the percentage:

SELECT
  Recipe_Ingredients.Recipe_ID,
  Recipe_Ingredients.Ingredient_Cost_Paid,
  Subquery.Total_Ingredient_Cost_Paid,
  CAST(Recipe_Ingredients.Ingredient_Cost_Paid / Subquery.Total_Ingredient_Cost_Paid * 100 AS DECIMAL(8,1)) AS [Ingredient Cost Allow as % of Total]
FROM
  Recipe_Ingredients
  INNER JOIN (
    SELECT
      Recipe_ID,
      SUM(Ingredient_Cost_Paid) AS Total_Ingredient_Cost_Paid
    FROM
      Recipe_Ingredients
    GROUP BY
      Recipe_ID
  ) AS Subquery ON Subquery.Recipe_ID = Recipe_Ingredients.Recipe_ID;

Note that I also cast the percentage column values to type DECIMAL(8,1) so that you do not get values with large fractions. The above query yields the following results:

Recipe_ID  Ingredient_Cost_Paid  Total_Ingredient_Cost_Paid  Ingredient Cost Allow as % of Total
---------  --------------------  --------------------------  -----------------------------------
12         4.65                  15.03                       30.9
12         0.40                  15.03                       2.7
12         9.98                  15.03                       66.4
27         5.35                  21.94                       24.4
27         12.50                 21.94                       57.0
27         1.09                  21.94                       5.0
27         3.00                  21.94                       13.7
65         2.35                  3.34                        70.4
65         0.99                  3.34                        29.6

As I said earlier, you will need to supply more information in your question if you need more specific help with your own situation. These queries and their results are just examples to show you what can be possible. Perhaps (and hopefully) this contains enough information to help you find a solution yourself. But you may always ask more specific questions, of course.