Updating table in mysql

In order to aggregate table data by time periods / ranges, we need a date/time column in the table we want to analyze.

Appropriate types for such a column include , but we can also use string / numeric types for grouping data together if they contain some sort of date/time information – though those will be much less flexible than native date/time types.

The reason for that is that one-to-many relationships cause rows to appear multiple times in the result, and thus be counted multiple times. This is also true if we want to get distinct values when counting a non-unique column.

updating table in mysql-77

Now we need to format and process this data using a server-side language (I use PHP), and prepare it for outputting using a graphing / charting solution in Javascript or Flash.

My favorite charting solution is a neat Javascript library called flot, which is very capable and customizable.

My SQL gives us many functions to extract aggregate data – including function, we typically want to get the number of different rows for each period.

This works well for single table queries, but when we start joining tables in our queries we might get surprising results.

By using our site, you acknowledge that you have read and understand our Cookie Policy, Privacy Policy, and our Terms of Service.

Your use of Stack Overflow’s Products and Services, including the Stack Overflow Network, is subject to these policies and terms.

So far we’ve dealt with the very simple case of counting rows from a single table.

Real world use-cases often involve multiple tables and extracting other types of aggregated data.

I usually opt to go with the format is limited by its date range (1970-01-01 to 2038-01-19), so you might need to use DATETIME if you’re using dates far in the future or past.

Assuming we have time-stamped data in our table, we can now start aggregating it into specific time periods.

This time around we do not have any native functions that extract both the month and year (though we could extract those separately and concatenate).

Tags: , ,