Menu
I'm trying to get my code to output in the following format:
january 2012 - 34
february 2012 - 23
where 34 and 23 would be a count of the total rows that fall within that month that have the id_dealership of 7. I need this to output all data for every month that an assignment was ever made.
The assignments table structure is as follows:
id_dealer (int)
date_assigned (int)
I've tried this but it does not work at all:
SELECT MONTH(date_assigned), YEAR(date_assigned), COUNT(*)
FROM assignments
GROUP BY MONTH(date_assigned), YEAR(date_assigned)
388
Use the MONTH() function to retrieve a month from a date/datetime/timestamp column in MySQL. This function takes only one argument – either an expression which returns a date/datetime/ timestamp value or the name of a date/datetime/timestamp column.
UNIX_TIMESTAMP() : This function in MySQL helps to return a Unix timestamp. We can define a Unix timestamp as the number of seconds that have passed since '1970-01-01 00:00:00'UTC. Even if you pass the current date/time or another specified date/time, the function will return a Unix timestamp based on that.
In computing, Unix time (also known as Epoch time, Posix time, seconds since the Epoch, Unix timestamp or UNIX Epoch time) is a system for describing a point in time. It is the number of seconds that have elapsed since the Unix epoch, excluding leap seconds. The Unix epoch is 00:00:00 UTC on 1 January 1970.
Unix epoch timestamps are supported in the following formats: 10 digit epoch time format surrounded by brackets (or followed by a comma). The digits must be at the very start of the message. For example, [1234567890] or [1234567890, other] followed by the rest of the message.
SELECT
MONTH(FROM_UNIXTIME(date_assigned)),
YEAR(FROM_UNIXTIME(date_assigned)),
COUNT(*)
FROM assignments
GROUP BY
MONTH(FROM_UNIXTIME(date_assigned)),
YEAR(FROM_UNIXTIME(date_assigned))
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
