[PHP-DB] MySQL Query help please!
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
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
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:[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
