Sum based on a criteria

[NarenMCA]

Hi,

Table Name: Countries
Field1: Ctry
Field2: State
Field3: Population

Table
--------------------------------------------------------------------
Ctry: State Pop
--------------------------------------------------------------------
Ctry1 State1 100
Ctry1 State2 100
Ctry1 State3 100
Ctry2 State1 50
Ctry2 State2 50
Ctry3 State1 100
Ctry3 State2 50
Ctry3 State3 100

I have the table in above format and i want to see the data in below format.
I used sum() but it throws "Data type mismatch" error.
Also I tried DSum but no use.
Can any one help me writing a query to sum the "Pop" field based on Country names in "Ctry" field?

------------------
Ctry: Pop
------------------
Ctry1 300
Ctry2 100
Ctry3 250

Thanks in advance,
Naren

[Stewart Ross]

Hi Naren, and Welcome to Bytes.

Simplest way to sum things is to use the Access query editor to do so, as it will generate the SQL in the background for you. In query design view select View, Totals to turn on summation and grouping (or press the sigma button on the menu bar, which shows the word "Totals" if you hover the mouse over it).

Group your query by country and use Sum on the population field just as you suggested, giving the field a new name (otherwise Access will give it a default new name of Sumoffieldname.

The SQL for your sum is just

SELECT Ctry, Sum([Population]) As TotalPopulation
FROM Countries
GROUP BY Ctry
ORDER BY Ctry

If you still find a type mismatch with the Sum then the population figures are likely to be text strings instead of numbers. If when viewing the data in your table the population figures appear left-justified instead of right-justified on screen (the way numbers are normally presented) it's a reasonable bet the data type of the population field is incorrect. In this case you would be best advised to correct the data type in your countries table. There are other workarounds (such as type conversion from text to long integer) but it's best to resolve incorrect types at source should this be found to be the case.

-Stewart

[NarenMCA]

Hi Stewart,

Its working. The information you have given is very helpful and easily understandable.

Thanks a lot.
Naren

Hi Naren, and Welcome to Bytes.

Simplest way to sum things is to use the Access query editor to do so, as it will generate the SQL in the background for you. In query design view select View, Totals to turn on summation and grouping (or press the sigma button on the menu bar, which shows the word "Totals" if you hover the mouse over it).

Group your query by country and use Sum on the population field just as you suggested, giving the field a new name (otherwise Access will give it a default new name of Sumoffieldname.

The SQL for your sum is just

SELECT Ctry, Sum([Population]) As TotalPopulation
FROM Countries
GROUP BY Ctry
ORDER BY Ctry

If you still find a type mismatch with the Sum then the population figures are likely to be text strings instead of numbers. If when viewing the data in your table the population figures appear left-justified instead of right-justified on screen (the way numbers are normally presented) it's a reasonable bet the data type of the population field is incorrect. In this case you would be best advised to correct the data type in your countries table. There are other workarounds (such as type conversion from text to long integer) but it's best to resolve incorrect types at source should this be found to be the case.

-Stewart