Combine variables and sum(variables) in single query

[showman13]

Good Morning...

First let me start by thanking those of you that respond with great education information in here.. You have made my learning curve (time invested) much shorter than it would have been with just a reference manual.

I know I still have a lot to learn, but this has become my favorite place to get direct answers when I need to get past something that is stumping me.

Now to the question.

I have 2 tables... one is the members table and the other is the reserve_rec table.
The members table has one record per member and the reserve_rec table may have many

What I would like to extract is a dataset that would contain all pertinent information(multiple fields) for each member from the members table that has records in the reserve_rec table by matching the mem_id from each, and where the expire_date in each record in the reserve_rec is Greater than a specified datetime field
AND at the same time I would like to sum(shares) field in the reserve_rec table GROUPED by the mem_id.

So, effectively I want a listing of the members with their name address, etc...
That would each have a listing of their non-expired reserves,
And a total of the non-expired reserve shares at any given time.

Simple Right? LOL I thought it wouldn't be too hard, but can't seem to get a query formed correctly to accomplish it.

Here is the closest I have gotten so far... Can someone help with the structure of this type of query? Or tell me if it isn't possible to do in a single query..?

SELECT m.mem_id, m.user, m.fname, m.lname, m.email, m.addr1, m.addr2, m.city, m.state, m.zip, m.country, r.shares, r.group_id, r.order_id, sum(shares)
FROM members as m, reserve_rec as r
ORDER BY r.mem_id, r.group_id
GROUP BY r.mem_id
WHERE m.mem_id=r.mem_id AND r.expire_date >= '2012-02-26 19:00:00'

This is the error I receive in PHP MyAdmin
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY r.mem_id
WHERE m.mem_id=r.mem_id AND r.expire_date >= '2012-02-26 19:00' at line 4

[showman13]

I guess it would help to start with a correctly formatted query...

But even with this, I'm not getting the results that I want to see...

SELECT m.mem_id, m.user, m.fname, m.lname, m.email, m.addr1, m.addr2, m.city, m.state, m.zip, m.country, r.shares, r.group_id, r.order_id, r.expire_date, sum(r.shares)
    FROM members as m, reserve_rec as r
    WHERE m.mem_id=r.mem_id AND r.expire_date >= '2012-02-26 19:00:00'
    GROUP BY r.mem_id
    ORDER BY r.mem_id, r.group_id

I'm getting a single record per member with active reserves,
It is the first record that is encountered, as far as the data cells are concerned,
and the sum(shares) IS giving me the correct number.

At least I'm getting a result set now.. Just not exactly what I need.

[smantscheff]

You cannot have aggregate and non-aggregate columns from the same table in your query. MySQL does a bad job in not noticing this common error.
If you want the single reserve_rec items together with the total of shares, you need two table clauses in your FROM clause. Consider this:

drop table if exists my_members;
drop table if exists my_reserve_rec;
create table my_members (mem_id integer);
create table my_reserve_rec (mem_id integer, shares integer, expire_date datetime);


insert into my_members values (1),(2);
insert into my_reserve_rec values (1,2,'2011-01-01');
insert into my_reserve_rec values (1,3,'2012-01-01');
insert into my_reserve_rec values (1,4,'2012-01-01');
insert into my_reserve_rec values (1,5,'2012-01-01');

SET @expire_date = '2012-01-01';
SELECT m.mem_id, r.shares, 
(select(sum(shares)) from my_reserve_rec as r2 
    WHERE m.mem_id=r2.mem_id AND r2.expire_date >= @expire_date
) as sum_shares
FROM my_members as m, my_reserve_rec as r
WHERE m.mem_id=r.mem_id AND r.expire_date >= @expire_date;

[Biiim]

the group by needs to be on the unique id for each row you want to pull,

eg. If you want to pull a list of users, you want the group by to be on the user id so you get no duplicates and i would keep the id group by on the primary table for that key(foreign tables could have nulls)

SELECT *
FROM members as m LEFT JOIN extra_data as x ON `m`.`mem_id` = `x`.`mem_id`
WHERE m.mem_id=x.mem_id AND x.expire_date >= '2012-02-26 19:00:00'
GROUP BY m.mem_id
ORDER BY m.mem_id, x.group_id

If table `x` has more than 1 row per user id (that passes the where query) it will be random which data will get pulled across in the join - the row that gets found first presumably.

If you want multiple rows for each user you will want to change the group by to the unique id on the joint table ie. instead of GROUP BY m.mem_id -> GROUP BY x.x_id
x_id being a incrementing unique id in the joint table

That will pull multiple rows such as login dates such as

x_id,mem_id, logindate
1,1,2012-02-22
2,1,2012-02-23
3,1,2012-02-26
4,2,2012-02-21
5,2,2012-02-22
6,2,2012-02-23

GROUP BY basically groups all identical values into the same row

[Biiim]

I just twigged shortly after it wasn't actually a group by question, i think you're query is fine and what you are asking isn't really possible with that setup - not very easily atleast.

The contradiction is wanting the reserve dates AND the sum of the shares

I would get the data out of the query as:
m.mem_id,r.order_id?,r.shares,r.date
1,1,5,2012-02-21
1,2,8,2012-02-22
1,4,12,2012-02-24
2,3,6,2012-02-23
2,5,18,2012-02-25
3,6,10,2012-02-26

and use php to add it up into an array.

//$result == mysqli result
$data = array();
while($row = mysqli_fetch_assoc($result)){
	if(!isset($data[$row['mem_id']){
		$data[$row['mem_id'] = array();
	}
	if(!isset($data[$row['mem_id']['shares']){
		$data[$row['mem_id']['shares'] = 0;
	}
	$data[$row['mem_id']['shares'] += $row['shares'];
}
foreach($data as $k=>$v){
	//$k == mem_id
	//$v['shares'] == sum of shares
}
//$data[1]['shares'] number of shares for member #1

[showman13]

I finally came to realize that I couldn't do what I was trying to do, so I ended up doing the primary query, and using a while loop on the results from that query, I created sub queries for the additional data needed for each primary record...

Thank you all for your feedback. It is always helpful to pose questions in here, as the act of asking the question spurs additional thought processes that when tied together with the responses here, creates a solution...

Thanks again
Douglas