MySQL Number Format

[captzero]

Hi all,
I am using the following code to call the 10 Wettest Days from my database:

Code: Select all

SELECT logdate, date_format(logdate, '%e %b %Y')as date, TotRainFall FROM `dayfile`  order by TotRainFall DESC limit 10 

It returns the result with two decimal places, ie, 100.90
I've tried using

Code: Select all

.......as date, TotRainFall, FORMAT (TotRainFall,1) FROM `dayfile`  order by TotRainFall ......

and although it doesn't throw any errors, it also returns the result with 2 decimal places. Working in mls, I'd like to knock off the superfluous 0 at the end. Am I even close?. Can someone point me in the right direction?
Thanks

[tobyspond]

Hi Dan,

Try round(totrainfall,1)

Kerry

[captzero]

Thanks Kerry, I'll give it a go after work.

[captzero]

Hi Kerry,
Thanks for the tip . Worked perfectly for wettest days once I learned where to correctly place it within the code. I thought I'd be able to also use round(totrainfall,1) in the same way for wettest and driest months but that's not the case. I have a working 'Top 10' page now that I'm reasonably happy with. I think I'll leave it alone now as its working pretty much as I want it.

http://www.brisbaneliveweather.com/Top10.php

And thanks again for all the assistance you've given me over the past few weeks.

[tobyspond]

Happy to help. Your page looks nice. Regarding the consecutive wet/dry day rounding issue. Did you try applying the round to the totrainfall in the query? It may work.

Kerry

[captzero]

Did you try applying the round to the totrainfall in the query

Yes I did try that but it made no difference. Not to worry. I can live with it.

[tobyspond]

this should work round(sum(totrainfall),1)

[mcrossley]

Hi Kerry,
Thanks for the tip . Worked perfectly for wettest days once I learned where to correctly place it within the code. I thought I'd be able to also use round(totrainfall,1) in the same way for wettest and driest months but that's not the case. I have a working 'Top 10' page now that I'm reasonably happy with. I think I'll leave it alone now as its working pretty much as I want it.

http://www.brisbaneliveweather.com/Top10.php

And thanks again for all the assistance you've given me over the past few weeks.

OK you got me, I've started one of these too. Just got all the basic SQL queries together, now to format the outputs...
http://weather.wilmslowastro.com/top10s-sql.php

As it only takes about 0.3 seconds to execute the queries on my old lappy, I think I may just make the page dynamic rather than have the additional maintenance of generating a static page afresh each day.

[captzero]

this should work round(sum(totrainfall),1)

And it does. Thanks again

[mcrossley]

Dan

My facsimile of your page hits its first incarnation...
http://weather.wilmslowastro.com/top10s.php

Still some 'prettying' to do, and I'm not sure what to do about the date formats.

[tobyspond]

Mark,

Add a date format to your query: date_format(logdate, '%b %e %Y'), which gives you short month, day, year.

Rearrange/alter to suite your needs


Kerry

[mcrossley]

Thanks Kerry, I was more thinking about what format I prefer - that is also 'compact'

[captzero]

Thanks Kerry, I was more thinking about what format I prefer - that is also 'compact'

Hi Mark,
Initially I wanted to include about half a dozen records as a 'Top 5' within another page but after a play around, decided to include all the data into its own page. I also wanted something I could read without my glasses on.

[captzero]

Kerry,
If I can impose again.....

Code: Select all

SELECT year, month, total
FROM (
SELECT  year( logdate ) AS year, MONTHNAME( logdate ) AS month, sum(TotRainFall), round(sum(totrainfall),1) AS total 
FROM `dayfile` 
GROUP BY year, month 
ORDER BY total DESC
) AS tmp  
LIMIT 10

produces Wettest Months as October 2013, January 2011 etc, For consistency and formatting I'd like short month name as Oct 2013, Jan 2011...
This formula is slightly more complex than the others I've used. I need to use '%b' but not sure where or in what context. I've googled and tried numerous combinations but I've hit a wall. Where have I gone wrong?
Thanks again

[mcrossley]

try

Code: Select all

DATE_FORMAT(LogDate,'%b %Y')