0

Use the same data for SQL & Google Sheets calculations but can't understand why in SQL and Google Sheets I have different values for the total. 2 use cases are described below

  1. I have 2 tables:
  2. 1st table data columns: ordernumber, sales number, status, month_id, dealsize
  3. 2nd table data columns: ordernumber, year_id, productline, city, country, territory
  4. The task is: to find out the number of motorcycle sales (Productline = ‘Motorcycles’) in 2003 by region (Territory)

I wrote such a request and got the answer

SELECT 
year_country.year_id year,
year_country.territory territory,
year_country.productline product,
sum(sales_size.sales) total_sales

FROM sales_size
JOIN year_country
ON sales_size.ordernumber = year_country.ordernumber
WHERE year_country.productline = "Motorcycles" AND year_id = "2003"
GROUP by year_country.territory
;

enter image description here

But when I re-checked the calculations just in Google Sheets I found out that the total is wrong. In Google Sheets, I just put the content of both table into one and then used SUMIF formula.

=sumifs([range],[range],"Motorcycles",[range],"NA",[range],"2003")
  • APAC = 12058.72
  • EMEA = 50956.2
  • NA = 65887.91
  1. Then the same 2 tables
  2. The task is: to find the average cost of the order (SALES) by year

I wrote such a request and got the answer

SELECT 
year_country.year_id year,
round(avg(sales_size.sales)) average_sales

FROM sales_size
JOIN year_country
ON sales_size.ordernumber = year_country.ordernumber
GROUP by year_id
;

enter image description here

But when I re-checked the calculations just in Google Sheets I found out that the average is also wrong. In Google Sheets, I just put the content of both table into one and then used AVERAGEIF formula.

=AVERAGEIF([range],[year],[range])
  • 2003 = 5454
  • 2004 = 5298
  • 2005 = 4958

Source tables

Vel Green
  • 67
  • 8
  • Please don’t link to, or use, images. Add all information directly to your question preferably as editable text – NickW Sep 29 '22 at 00:17
  • As I mentioned in your previous question, it makes no sense to include columns like `year_id` and `product` in the result, since there are lots of different values of those columns in each territory. – Barmar Sep 29 '22 at 00:25
  • Without seeing how you're doing the sums in Google Sheets, it's hard to explain why the results are different. – Barmar Sep 29 '22 at 00:27
  • There are 3 rows for `ordernumber = 10107` in each table. You're getting a cross product of them before you sum, so it multiplies the sum by 3. – Barmar Sep 29 '22 at 00:30
  • @Barmar I've just put these 2 tables inside one and used AVERAGEIF/SUMIF to recheck values https://docs.google.com/spreadsheets/d/1tPJC0rrBMlNQ6Y7qC87eOBRsDP3wuyGYz7cTAh0ewvw/edit?usp=sharing – Vel Green Sep 29 '22 at 11:23
  • @NickW unfortunately, you can't share code in sqliteonline – Vel Green Sep 29 '22 at 11:24
  • 1
    @VelGreen Combining into a single table is not at all like joining. – Barmar Sep 29 '22 at 14:02

0 Answers0