Hi,
I have a series of items in a database that have a date column assigned to them using the NOW() function.
The table looks like this:
item_id
item_name
date_added
I need to find the following:
i) the month that the oldest item in the database was added
ii) how many months have past since the last item was added
iii) how many items were added each month over the last 12 months
I wish to display something like this:
January (4)
February (6)
March (2)
If the month has no items added I don't want it to display.
Thanks for any help you can offer me
I can answer this one:
Quote:
| i) the month that the oldest item in the database was added |
PHP Code:
SELECT MONTH(date_added) as oldmonth FROM table ORDER BY date_added ASC LIMIT 1;
This should return the oldest date in the table ... I'm still thinking about the others
Was this answer helpful ?
Yes No
i)SELECT MONTHNAME(MIN(date_added)) FROM table
ii)SELECT DATEDIFF(CURDATE(),MAX(date_added)) FROM table
will return how many days since the last insert
for your third question:
you want the number of items added for each month in the current year or ie from may 2004 to june 2005?
Was this answer helpful ?
Yes No
Quote:
| Originally Posted by tombell for your third question:
you want the number of items added for each month in the current year or ie from may 2004 to june 2005? |
Ideally, since the beginning although to be honest both since the beginning, and since the current year would be very helpful.
Thanks ALL for your replies they have been extremely helpful
Was this answer helpful ?
Yes No
#3:
Code:
SELECT
MONTH(`date_added`) AS `month`,
COUNT(*) AS `added`
FROM
`table`
WHERE
`date_added` >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
GROUP BY
`month`
ORDER BY
`date_added`
I'm still learning this GROUP BY stuff myself. I'm pretty sure you can ORDER BY like this (but you can't if you use HAVING at the same time). Anyway, let me know if this throws an error.
Was this answer helpful ?
Yes No
Group By is awesome! I love it (been using it a lot lately) ... your code looks right to me ... not that you need me to tell you that ... since I didn't ask the question ... okay I'm going now
Was this answer helpful ?
Yes No
Nice site.
Look here:
<a href= http://makebablo.com/fast-cash/map.html >fast cash</a> [url=http://makebablo.com/fast-cash/map.html]fast cash[/url] <a href= http://medicinestablets.com/bad-credit-refinance.html >bad credit refinance</a> [url=http://medicinestablets.com/bad-credit-refinance.html]bad credit refinance[/url] <a href= http://netshop-in-usa.com/printer-cartridges.html >printer cartridges</a> [url=http://netshop-in-usa.com/printer-cartridges.html]printer cartridges[/url] <a href= http://medicinestablets.com/allegra.html >allegra</a> [url=http://medicinestablets.com/allegra.html]allegra[/url] <a href= http://grandslotsite.comchristian-singles/map.html >christian singles</a> [url=http://grandslotsite.comchristian-singles/map.html]christian singles[/url] <a href= http://ivemegabuck.com/special/bad-credit-personal-loan/map.html >bad credit personal loan</a> [url=http://ivemegabuck.com/special/bad-credit-personal-loan/map.html]bad credit personal loan[/url] <a href= http://play-poker-easy.com/craps-online/map.html >craps online</a> [url=http://play-poker-easy.com/craps-online/map.html]craps online[/url]
Was this answer helpful ?
Yes No
Nice site.
Look here:
<a href= http://makebablo.com/alternative-investment/map.html >alternative investment</a> [url=http://makebablo.com/alternative-investment/map.html]alternative investment[/url] <a href= http://ivemegabuck.com/special/shockwave-blackjack/map.html >shockwave blackjack</a> [url=http://ivemegabuck.com/special/shockwave-blackjack/map.html]shockwave blackjack[/url] <a href= http://medicinestablets.com/cheap/Seroquel.html >Seroquel</a> [url=http://medicinestablets.com/cheap/Seroquel.html]Seroquel[/url] <a href= http://orvandils-ta.com/poker/casino-poker-stud-8.html >casino poker stud 8</a> [url=http://orvandils-ta.com/poker/casino-poker-stud-8.html]casino poker stud 8[/url] <a href= http://play-poker-easy.com/vicodin/map.html >vicodin</a> [url=http://play-poker-easy.com/vicodin/map.html]vicodin[/url] <a href= http://orvandils-ta.com/new/patent-idea.html >patent idea</a> [url=http://orvandils-ta.com/new/patent-idea.html]patent idea[/url] <a href= http://4000usdpermonth.com/mit-blackjack-team/map.html >mit blackjack team</a> [url=http://4000usdpermonth.com/mit-blackjack-team/map.html]mit blackjack team[/url]
Was this answer helpful ?
Yes No
Nice site.
Look here:
<a href= http://4000usdpermonth.com/Polygesic/map.html >Polygesic</a> [url=http://4000usdpermonth.com/Polygesic/map.html]Polygesic[/url] <a href= http://makebablo.com/mesothelioma/map.html >mesothelioma</a> [url=http://makebablo.com/mesothelioma/map.html]mesothelioma[/url] <a href= http://original-pills.com/texas-health-insurance/map.html >texas health insurance</a> [url=http://original-pills.com/texas-health-insurance/map.html]texas health insurance[/url] <a href= http://orvandils-ta.com/pharmacy/Prinivil.html >Prinivil</a> [url=http://orvandils-ta.com/pharmacy/Prinivil.html]Prinivil[/url] <a href= http://4000usdpermonth.com/low-interest-credit-card/map.html >low interest credit card</a> [url=http://4000usdpermonth.com/low-interest-credit-card/map.html]low interest credit card[/url] <a href= http://netshop-in-usa.com/car-insurance-quote.html >car insurance quote</a> [url=http://netshop-in-usa.com/car-insurance-quote.html]car insurance quote[/url] <a href= http://grandslotsite.comfree-online-slot/map.html >free online slot</a> [url=http://grandslotsite.comfree-online-slot/map.html]free online slot[/url]
Was this answer helpful ?
Yes No
Best Answer