i had problem when trying to sum up the values in my table. it does not contain any error but the problem is when i sum up the value in excel appear different from the value of my query.
Here is what i did
SELECT FORMAT(SUM(total_area),4) AS overall_area
SELECT country, FORMAT(SUM(accepted_area),4) AS total_area
GROUP BY country ORDER BY country )
the output from this is 21,755.4188. when i sum up the actual value using excel is 16542.2742
this is my first time posting here. if i lack any information, please guide me.
How many rows? Can you check manually.
If not use a subset to that you can check - you need to find which is right and check you are using the same data in both.
Simplify the query and get rid of the format function.
Then you can restrict the query in sql and excel until you narrow down the issue.
i tried to find out why ???
the way i did was by first creating sample data
drop create sample data
drop table farm
create table farm
insert into farm select 'india',100
insert into farm select 'india',200
insert into farm select 'india',50
insert into farm select 'u.s',10
insert into farm select 'u.s',30
insert into farm select 'u.s',40
insert into farm select 'u.s',20
select * from farm
select SUM(accepted_area) from farm
SELECT SUM(total_area) AS overall_area
SELECT country, SUM(accepted_area) AS total_area
GROUP BY country )
Maybe the format is the problem ???
i mean its cutting off
if data is 1234567
and you use format(data,4)
then data 1234 only is taken
or in excel
you may have forgotten to SELECT all rows
hope this helps
Argument data type int is invalid for argument 2 of format function.
Hi @nigelrivett, @harishgg1, @Lewie
thank you so much for your solution and explanation. it works. i thought including format wont effect the data since the number had decimal.
there is another problem. actually i have to find overall total farmer in state. below are the result i got from running my query.
SELECT state,COUNT(DISTINCT(refer_id)) AS farmer,
FORMAT(SUM(accepted_area),4) AS area
FROM `farm` GROUP BY state ORDER BY state
i tried using this query to find total for farmer.
SELECT SUM(farmer) as overall_farmer
from (SELECT state,COUNT(DISTINCT(refer_no)) AS farmer
FROM `farm` GROUP BY state) as overallx //output: 17478
i used same query to find total farmer in country. the result is fine. why would the value be different though i am using same query as the previous?
thank you for your guide.