query difference

gudii9
gudii9 used Ask the Experts™
on
SELECT COUNT(POPULATION) from CITY
WHERE District= "California";

what is difference between above and below query?

SELECT SUM(POPULATION)
FROM CITY
WHERE DISTRICT = "California";

Please advise
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
One counts the number of rows.  One adds up all the values.
Mark GeerlingsDatabase Administrator
Commented:
A "count" query will usually return a value much lower than a "sum" query.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
If population is:
10
20
30
40

COUNT will return 4 (because there are 4 values),
SUM will return 100 (because 10+20+30+40 = 100)
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Geert GOracle dba
Top Expert 2009
Commented:
count population ...
the database is not going to count the people, it's counting the rows in the database

it would be nice if the database could send out some minions to actually go count the people
but that's still fiction
In words which are understandable by human:
COUNT(POPULATION)  returns the number of cities where the population is known in District California
SUM(POPULATION)  returns the total population of all cities where the population is known in District California
(known = value different from NULL)

More technical summary:
If the data in your database are correct and if they are related to USA then COUNT() should return 0 and SUM() should return NULL because there is no District named California in USA.

Author

Commented:
If population is:
10
20
30
40

in above
COUNT(POPULATION)  returns the number of cities where the population is known in District California
SUM(POPULATION)  returns the total population of all cities where the population is known in District California

10 is number of city1?
20 is number of city2?

i am not clear on this
Please advise
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>i am not clear on this

Count does just that:  Counts objects starting at 1 and increment by one for every value returned.  The number the value contains doesn't matter.
https://en.wikipedia.org/wiki/Counting

Sum adds up the values returned in the column.
https://en.wikipedia.org/wiki/Summation

So in the example above:  4 values are returned so the COUNT is 4.

Adding (SUM) up all the rows returned returns 100.

10+20+30+40=100
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
Set up a quick test and see it in action:
drop table tab1 purge;
create table tab1(col1 number);
insert into tab1 values(12345);
insert into tab1 values(54321);
commit;

select count(col1), sum(col1) from tab1;

COUNT(COL1)  SUM(COL1)
----------- ----------
          2      66666

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial