help with query using NOW()

ericsante

SELECT TransResult, COUNT() AS HowMany, (COUNT() / total ) 100 AS Percent FROM tbltranslog, (SELECT COUNT() AS total FROM tbltranslog WHERE EXTRACT(MONTH FROM Date ) = EXTRACT(MONTH FROM NOW())) AS myTotal GROUP BY transresult

in the database Date is stored at mm/dd/yyyy the problem is I get the following error Truncated incorrect datetime value: '12/19/2006'.

can someone assist or provide a better way of doing this. what I am trying to do is to calculate the current %'s for the current month. It would be nice to go backwards also but not required now 😉

Thanks.

bogu

U can extract month from that date format like this: LEFT(Date,2) ...

liquorvicar

I assume you're using MySQL. Are you using one of the readymade DATE/TIME column types to store your data?

ericsante

nope I'm storing the data right now as varchar, I could change it if it would make a differance.

liquorvicar

It's certainly much easier to manipulate dates in MySQL using the correct column types. There are a whole load of DATE/TIME functions in MySQL that you can use. My one caveat would be that I'm not show how much of MySQL's datetime implementation is part of the SQL standard and how much is proprietary. Certainly if you've no major plans to port over to a different database, use the DATETIME column types.

cahva

Yep. Convert the dates to date column. If you leave it to varchar, it will pose a problem if you need to for example order by date. It can be done with that also but you would need to convert the date anyway in the select.

The type of date in SQL standard is YYYY-MM-DD H:i:s so its ok and if you ever need to port to other database its no problem. If you want to be sure its standard, then use TIMESTAMP which certainly is a standard. Nowdays TIMESTAMP's format is the same as DATETIME.

To quickly transform your date field you can do this:

UPDATE `tbltranslog` SET Date=CONCAT(SUBSTRING(Date,7,4),'-',SUBSTRING(Date,1,2),'-',SUBSTRING(Date,4,2));

And after that change the type(if you need it to be TIMESTAMP then replace DATE with that):

ALTER TABLE `tbltranslog` CHANGE `Date` `Date` DATE NOT NULL DEFAULT '0000-00-00';

Then if you need that date in mm/dd/yyyy format, it can be done in your select query like this:

SELECT DATE_FORMAT(Date,'%m/%d/%Y') Date FROM tbltranslog;

ericsante

awesome, thanks, and I assume that this will work also?

SELECT DATE_FORMAT(Date,'%Y/%m/%d') Date FROM tbltranslog;

cahva

Yes. I think you should read Date and time functions section from mysql manual. Its very enlightning and educational 🙂

ericsante

so this is what I ended up with, however when I run this query, I get data from november being included.... Also I do not get the Percent displayed.

SELECT TransResult, COUNT() AS HowMany, (COUNT() / total ) 100 AS Percent FROM tbltranslog, (SELECT COUNT() AS total FROM tbltranslog WHERE MONTH(Date) = MONTH(NOW())) AS myTotal GROUP BY transresult

liquorvicar

As I understand your query, you're not actually putting any WHERE conditions on your query - the WHERE clause is part of your sub-query. Did you want the WHERE condition to extend to the main query as well as the sub-query?

ericsante

yes I did.

liquorvicar

Something like this:

SELECT TransResult, COUNT(*) AS HowMany, (COUNT(*) / _total ) * 100 AS Percent FROM tbltranslog, (SELECT COUNT(*) AS _total FROM tbltranslog) AS myTotal WHERE MONTH(Date) = MONTH(NOW()) GROUP BY transresult

I'm guessing you're trying to calculate what percentage of ALL trans logged fall in a particular month right?