urm, so it does..,
thank you for your polite reply, I had included a month element in my query but left
it out cos i didnt think it made a dufference, of course i was using a where month = 1
clause after the ON bit which was limiting the link to those records, duh!
cheers
Dan
-Original Message-
From: Mark [mailto:[EMAIL PROTECTED]
Sent: 12 March 2003 15:36
To: Griffiths, Daniel
Cc: [EMAIL PROTECTED]
Subject: Re: [PHP-DB] MySQL Query help please!
I just set up a test db using your info, and the query you have
worked ok. It provided a zero. Here's what I have (slightly modified
from yours, but not appreciably).
select ports.port, sum(stats.amount) as Total from ports left join
stats on ports.port=stats.to_port group by ports.port order by Total
desc
Data:
Ports
-
UK
France
Germany
Italy
Stats
-
France 1000
Italy 3000
UK 2000
France 1000
UK 5000
Result:
UK 7000
Italy 3000
France 2000
Germany 0
--- Griffiths, Daniel [EMAIL PROTECTED] wrote:
Hi all,
I have 2 tables that I need to extract data from, say PORTS and
STATS.
PORTS contains a list of Sea Ports in PORT i.e. : -
PORTS.PORT
UK
FRANCE
GERMANY
NETHERLANDS
SPAIN
ITALY
STATS contains a few thousand records that with among other things
has the elements TO_PORT and AMOUNT.
the entries in TO_PORT match the ports in PORT.
Now one of the things I want to do is produce a summary of the
totals of AMOUNT against the ports in PORTS, including (and this is
the important bit that I'm stuck on) the ports in PORTS that do not
have a match in STATS.TO_PORT. so that I get a result such as : -
UK300
FRANCE100
GERMANY 400
NETHERLANDS 0
SPAIN 0
ITALY 300
and NOT :-
UK300
FRANCE100
GERMANY 400
ITALY 300
Which is all I can get at the moment.
the query I am using is : -
SELECT PORTS.PORT, SUM(STATS.AMOUNT) FROM PORTS LEFT JOIN STATS ON
PORTS.PORT = STATS.TO_PORT GROUP BY PORTS.PORT
Basicaly what I want to do is pull out a list of all entries in
PORTS.PORT and put a total figure against it from STATS.AMOUNT
where the STATS.TO_PORT matches PORTS.PORT and just a zero if
theres no entry in STATS.
Anyone got any ideas?
Thanks
Dan
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
=
Mark Weinstock
[EMAIL PROTECTED]
***
You can't demand something as a right unless you are willing to fight to death to
defend everyone else's right to the same thing.
***
__
Do you Yahoo!?
Yahoo! Web Hosting - establish your business online
http://webhosting.yahoo.com
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php