Select sum had different value when total up

Transact-SQL
1

Hi all,

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 
    from ( 
    SELECT country, FORMAT(SUM(accepted_area),4) AS total_area 
    FROM `farm` 
    GROUP BY country ORDER BY country ) 
    as overallx

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.
Thank you.

2

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.

select sum(accepted_area)
from farm

Then you can restrict the query in sql and excel until you narrow down the issue.

1 Like
3

hi

i tried to find out why ???

the way i did was by first creating sample data

drop create sample data 
drop table farm
go 

create table farm
(
country varchar(10), 
accepted_area int
)
go 

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
go 

select * from farm
go

select SUM(accepted_area) from farm
go

450

SELECT SUM(total_area) AS overall_area
from (
SELECT country, SUM(accepted_area) AS total_area
FROM farm
GROUP BY country )
as overallx

also giving
450

Maybe the format is the problem ???
FORMAT(SUM(accepted_area),4)
FORMAT(SUM(total_area),4)
i mean its cutting off
example
if data is 1234567
and you use format(data,4)
then data 1234 only is taken

just guessing

or in excel
you may have forgotten to SELECT all rows

hope this helps
:slight_smile:
:slight_smile:

1 Like
4

SELECT FORMAT(123456.123456,4)
Argument data type int is invalid for argument 2 of format function.

1 Like
5

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. :slightly_smiling_face:

6

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

daerah
daerah.JPG216×544 27.3 KB

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.