| Mailing List | | Home | | MySQL General - General MySQL discussion | | MaxDB - Everything about MaxDB, formerly known as SAP DB | | MySQL on Win32 - Runing MySQL on Windows 9x/Me/NT/2000/XP | | Java Help - Mostly related to the MySQL Connector/J driver | | ODBC - ODBC with the MySQL Connector/ODBC driver | | Perl - Perl support for MySQL with DBI and DBD::mysql | | MySQL++ - Programming with the C++ API to MySQL |
|
|
 | | | A join I can not wrap my head around... | A join I can not wrap my head around... 2004-03-15 - By Garth Webb
Back
create table account (
id int(11),
state varchar(2)
);
create table transaction (
id int(11),
user_id int(11),
amount int(6),
added timestamp
);
select sum(transaction.amount)
from transaction, account
where transaction.id = account.id AND
account.state = 'TX ' AND
transaction.added between '20030101000000 '
and '20031231235959 ';
On Mon, 2004-03-15 at 14:37, Scott Haneda wrote:
> Account table has a field state and a field id.
> Transaction table has fields id, user_id, amount, added
>
> Id is the PK in the account table, related to the user_id in the transaction
> table.
>
> I need to get a sum of the amount column for all users in state of TX in the
> year of 2003.
>
> Tried a few ways, cant seem to get this one.
So, given:
create table account (
id int(11),
state varchar(2)
);
create table transaction (
id int(11),
user_id int(11),
amount int(6),
added timestamp
);
Did you try:
select sum(transaction.amount)
from transaction, account
where transaction.id = account.id AND
account.state = 'TX ' AND
transaction.added between '20030101000000 '
and '20031231235959 ';
?
--
|- Garth Webb -|
|- garth@(protected) -|
|
|
|