sum problem

[rahia307]

i have two tables
one table contain the basic information about the users.
second table contain amount information of users.
i want to sum of amount of users Country vise. i have country id in users table.
but in second table have the user id and amount information.
how i can get the sum of users amount country vise.
if any one have idea then please help me.

[Purple]

Hi,

You refer to tables, is it safe to assume there is a database of some sort ? Can you give a little more info on what environment you are running plse

Purple

[ak1dnar]

i have two tables
one table contain the basic information about the users.
second table contain amount information of users.
i want to sum of amount of users Country vise. i have country id in users table.
but in second table have the user id and amount information.
how i can get the sum of users amount country vise.
if any one have idea then please help me.

if this user id is repeating in both the tables you can make it.

userinfo table

user_id - userName - user_country_id
1001 - Mike - 5
1002 - Sam - 2
1003 - Jane - 5

amountinfo table

amt_id - user_id - amount
1 - 1001 - 100
2 - 1002 - 150
3 - 1003 -250

SELECT sum(amount) 
FROM amountinfo
LEFT JOIN userinfo ON amountinfo.user_id = userinfo.user_id
WHERE userinfo.user_country_id = '5'

[rahia307]

hi
first of all thanks for reply.
now i tell u the structure of data base.
basically i have three tables.
1. country
this table contain the following fields.
id_zone, country_name
2. user_info.
this table contain the following fields.
id, user_name, id_zone
3. amount.
this table contain the following fields.
id, id_user, amount
amount table contain multiple amount of id_user
now i want to get the sum of amount of users country vise .
help me about this problem.
i will be thanks full .

[Purple]

Hi,

this is the SQL based on MSSQL - not sure if it will work on other RDBMS platforms - which is why I asked what the environment was on an previous post..

SELECT     SUM(amount.amount) AS sum_amount, country.country
FROM         amount INNER JOIN
                      user_info ON amount.id_user = user_info.id INNER JOIN
                      country ON user_info.id_zone = country.id_zone
GROUP BY country.country

Regards Purple

[rahia307]

Hi,

this is the SQL based on MSSQL - not sure if it will work on other RDBMS platforms - which is why I asked what the environment was on an previous post..

SELECT     SUM(amount.amount) AS sum_amount, country.country
FROM         amount INNER JOIN
                      user_info ON amount.id_user = user_info.id INNER JOIN
                      country ON user_info.id_zone = country.id_zone
GROUP BY country.country

Regards Purple

hi
thanks but it not work properly .

[ak1dnar]

Post your SQL queries for table structures with some sample values.

NOTE : As purple asked what is the SQL environment that you have used for this.
(MySQL,MsSQL,etc)

[rahia307]

hi
i use mysql database
and send a sample of query
$sql =mysql_query("SELECT *
FROM $tbl_users u, $tbl_gd_country c
WHERE u.id_zone=c.id_zone
GROUP BY u.id_zone
ORDER BY c.id_zone ");
$j=0;
while($result = mysql_fetch_array($sql)) {
$sql_quran = "SELECT sum(amount)
FROM $tbl_amount m,$tbl_users u
WHERE m.id_user='$result[id]'
AND u.id=m.id_user
";
one thing i tell here i have multiple entry of a same id_user in amount table

[rahia307]

hi
i use mysql database
and send a sample of query
$sql =mysql_query("SELECT *
FROM $tbl_users u, $tbl_gd_country c
WHERE u.id_zone=c.id_zone
GROUP BY u.id_zone
ORDER BY c.id_zone ");
$j=0;
while($result = mysql_fetch_array($sql)) {
$sql_quran = "SELECT sum(amount)
FROM $tbl_amount m,$tbl_users u
WHERE m.id_user='$result[id]'
AND u.id=m.id_user
";
one thing i tell here i have multiple entry of a same id_user in amount table

[Purple]

misread previous post

[Purple]

Hi,

Having looked at your code can we just pause for a moment and review what it is your are trying to get as a results set - my 'guess' is amount by user by country ?

are you also using the array returned from the first SQL query - is there are reason you have not done this as one SQL query ?

Apologies for the numerous questions but if we clarify at the start we will all save time :)

Purple

[ak1dnar]

hi
i use mysql database
and send a sample of query
$sql =mysql_query("SELECT *
FROM $tbl_users u, $tbl_gd_country c
WHERE u.id_zone=c.id_zone
GROUP BY u.id_zone
ORDER BY c.id_zone ");
$j=0;
while($result = mysql_fetch_array($sql)) {
$sql_quran = "SELECT sum(amount)
FROM $tbl_amount m,$tbl_users u
WHERE m.id_user='$result[id]'
AND u.id=m.id_user
";
one thing i tell here i have multiple entry of a same id_user in amount table

I requested the SQL query for table structure i think.

example:

-- 
-- Table structure for table `users`
-- 
 
CREATE TABLE `users` (
  `id` int(10) NOT NULL auto_increment,
  `region` varchar(250) default NULL,
  `staffid` varchar(250) default NULL,
  `firstname` varchar(250) default NULL,
  `surname` varchar(250) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;
 
-- 
-- Dumping data for table `users`
-- 
 
INSERT INTO `users` VALUES (1, 'region1', 'staff1', 'Mike', 'Conors');
INSERT INTO `users` VALUES (2, 'region2', 'staff2', 'Akon', 'white');
 
 

If you using phpMyAdmin export them for the current tables.

[rahia307]

hi
i cannot understand this
plz help me

[ak1dnar]

hi
i cannot understand this
plz help me

Read these posts carefully !
http://www.thescripts.com/forum/post2625796-7.html
http://www.thescripts.com/forum/post2626374-12.html

Do you have set up the MySQL tables, then can i get the Structured Query language that you used to create those tables. with Insert statement for couple of records.

I clearly mentioned what i need, by giving this example SQL query for a sample table structure.
How may i help you if you are not reading the posts carefully.

[Purple]

Hi,

or you could try replacing both of your queries with :

SELECT     SUM(amount.amount) AS amount, user_info.user_name, user_info.id_zone, country.id_zone AS country_zone, country.country
FROM         user_info INNER JOIN
                      amount ON user_info.id = amount.id_user INNER JOIN
                      country ON user_info.id_zone = country.id_zone
GROUP BY user_info.id, user_info.user_name, user_info.id_zone, country.id_zone, country.country
ORDER BY user_info.id_zone

If that doesn't do it for you, follow Ajaxrands suggestion and I am sure we can get through this...

Purple