fenway

It's unclear as to the source of the error, I agree...

There are many stickies on this forum explicitly for that purpose.

That doesn't make any sense.

usually INT UNSIGNED is the way to go... that's about 8 billion.

The mysql refman page of date/time functions has many examples of these types of things... see the comments at the bottom of the page.

Likely header info... but impossible to tell without seeing the first 10 lines.

You want SUM(IF angler = 'Oz', 1, 0 ) AS anglerA not COUNT(CASE WHEN angler = 'Oz' THEN species_id ELSE 0 END ) AS anglerA

And don't call an index "index".

I want to see the output from mysql, NOT the output from php...

I do agree... this isn't normalization at any price, it's normalization FIRST, and then de-normalization MUCH LATER. I doubt that the OP has such an application.

EXPLAIN is a mysql modifier to SELECT; perhaps you should look at the sticky on posting questions if you're not sure how.

Run the COUNT() first, then use this as a dervied table, and SUM that.

Once you assign another variable's value, it stay around until it goes out of scope.

LOL... Thanks I'll PM it to you. Not helpful the rest of us... post on this thread, please. Besides, what output did you get from the first query you posted?

You can't aggregate an aggregate in a single query...

No longer -- in MySQL5, up to 65K.

Some EXPLAIN output, perhaps?

That's a PHP issue -- I assume the mysql queries return the desired results.

Also known as a "pivot table" -- see this PDF.

Small, uneditable, permanent lists, like the kind that don't exist in the real world. It's a cheat to bypass proper normalization for a minute performance optimization. Not worth it, IMHO -- and just because an API can hide these details, doesn't make it "easy", it makes it "transparent". Having to change the DB schema to add another option is ridiculous.

He does. The problem was with not cleaning the data sent from web interface (separation signs). So he's NOT... integers don't have non-digit characters.

Hey, how come no one ever sucks up to me? :-P

The only checkbox that is tricky in the one that spans multiple columns... simply translate this into an OR.

This is a standard cross-tab query... you can use PERIOD() to get the YYMM range (since they all seems to be full month), and then you can simply SUM() these (though there appears to be just a single entry). No need for PHP at all.

DATETIME is much better, IMHO.