RE: MySQL Query help please!

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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:  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

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]

  Powered by Linux