RE: [PHP-DB] MySQL Query help please!

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:mark_weinstock@yahoo.com] Sent: 12 March 2003 15:36 To: Griffiths, Daniel Cc: php-db@lists.php.net 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" <DANIEL.GRIFFITHS@conconf.org> 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 : - > > UK 300 > FRANCE 100 > GERMANY 400 > NETHERLANDS 0 > SPAIN 0 > ITALY 300 > > and NOT :- > > UK 300 > FRANCE 100 > 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 mark_weinstock@yahoo.com *************************************** 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