compare temporary columns in sql-CodePudding

I have a table that includes small_bags, large_bags, xlarge_bags and total_bags.

I run this query to create a temporary column total_calc which is the same as total_bags.

SELECT 
  region,
  total_bags,
  Small_Bags Large_Bags XLarge_Bags as total_calc
FROM `coursera-data-analyst-356606.avocado_data.avocado_prices`

I just want to compare whether there are any mistakes in total_bags. what query should I run next to find out which rows differ in total_bags and total_calc columns? After some research, as I understood, I have to run where Total_Bags <> total_calc but it does not work if I put the where clause in this query. I tried to run this query but it won't work. I believe, I have to create a subquery and include a where clause there but I don't know how to do it. Could someone help me with this?

SELECT 
  region,
  total_bags,
  Small_Bags Large_Bags XLarge_Bags as total_calc
FROM `coursera-data-analyst-356606.avocado_data.avocado_prices` 
where Total_Bags <> total_calc

CodePudding user response：

You are almost there, ALIAS cannot be used directly in where clause within the same block. Instead try:

SELECT 
  region,
  total_bags,
  Small_Bags Large_Bags XLarge_Bags as total_calc
FROM `coursera-data-analyst-356606.avocado_data.avocado_prices` 
where ROUND(Total_Bags,2) <> ROUND((Small_Bags Large_Bags XLarge_Bags),2)

Or you can use a temporary table:

SELECT * FROM (
SELECT 
  region,
  ROUND(total_bags,2) as totalBags,
  ROUND(Small_Bags Large_Bags XLarge_Bags,2) as total_calc
FROM `coursera-data-analyst-356606.avocado_data.avocado_prices` ) a
where totalBags <> total_calc