How to calculate percentage of total for each column in Teradata SQL?

Data and Databases
1

I’m working on a Teradata SQL query and need help adding percentage columns. My current query gives me revenue data for restaurants by week, meal, and product type. Now I want to show what percent each meal contributes to the total food and beverage revenue for that week and restaurant.

Here’s a simplified version of my current query:

SELECT week, restaurant,
  CASE
    WHEN CAST(timestamp AS TIME FORMAT 'HH:MI:SS') BETWEEN '03:00:01' AND '10:00:00' THEN 'Morning'
    WHEN CAST(timestamp AS TIME FORMAT 'HH:MI:SS') BETWEEN '10:00:01' AND '14:59:00' THEN 'Midday'
    ELSE 'Evening'
  END AS dining_period,
  SUM(revenue) AS total_sales,
  SUM(CASE WHEN product_category = 'meals' THEN revenue ELSE 0 END) AS meal_sales,
  SUM(CASE WHEN product_category = 'drinks' THEN revenue ELSE 0 END) drink_sales
FROM sales_data
GROUP BY 1, 2, 3
ORDER BY 1, 2, 3

How can I modify this to add columns showing the percentage each dining period contributes to the total meal and drink sales for that week and restaurant? For example, if morning meal sales are 200 out of a total 660 for the week, it would show 30.30%.

2

To add percentage columns for each dining period’s contribution to total meal and drink sales, you can use window functions in Teradata SQL. Here’s how you could modify your query:

SELECT 
  week, restaurant, dining_period, total_sales, meal_sales, drink_sales,
  (meal_sales / SUM(meal_sales) OVER (PARTITION BY week, restaurant)) * 100 AS meal_percent,
  (drink_sales / SUM(drink_sales) OVER (PARTITION BY week, restaurant)) * 100 AS drink_percent
FROM (
  -- Your original query here
) subquery
ORDER BY week, restaurant, dining_period

This approach calculates the percentages by dividing each period’s sales by the total for that week and restaurant, then multiplying by 100. The OVER clause with PARTITION BY ensures the percentages are calculated within each week-restaurant combination. This method avoids repeating the complex logic of your original query and simply wraps it in a subquery.

3

ooh, interesting question! have you thought about using the RATIO_TO_REPORT function? it might simplfy things. like this:

RATIO_TO_REPORT(meal_sales) OVER (PARTITION BY week, restaurant) * 100 AS meal_pct

Could be easier than dividing manually. what do you think? have you tried any other methods?

4

you can use window functions to calculate percentages. try this:

SELECT *,
(meal_sales / SUM(meal_sales) OVER (PARTITION BY week, restaurant)) * 100 AS meal_pct,
(drink_sales / SUM(drink_sales) OVER (PARTITION BY week, restaurant)) * 100 AS drink_pct
FROM (your original query) sq
ORDER BY week, restaurant, dining_period

this calculates %s for each dining period within week/restaurant.