Month query from MySql

Pigmaster

Hi there,

I am trying to build a mysql query to produce a graph.

I have a field (datefly) in a table(flights), the datefly field contains the following records

2004/01/13
2004/01/16
2004/03/03
2004/03/12
2004/03/20
2004/04/12

What I am trying to work out is a sql query that will produce something similar to
Jan,2
Feb,0
March,3
April,1

ie the sum of flights each month, then with this data I will push out to baachart

I've tried using sum and count but can not seem to get the results.

Can any body shed some light.

Thanks

devinemke

assuming datefly is a DATE or DATETIME field (it does not look like it from your post):

SELECT MONTHNAME(datefly), COUNT(*) FROM flights GROUP BY MONTH(datefly)

note that this query does not take the year into account, it will group all records by month regardless of year.

Pigmaster

Thanks

I try out the query, my current test is

SELECT
count(flights.Datefly) AS CountOfMonths
FROM
flights
group by
Datefly

It brings the count of each month

The field is a mysql date field the / is stored as - in the table

The next step will be to work on the year and for blank months.

Thanks again

kpzani

make a table as follows

CREATE TABLE months (month varchar(20) primary key);

then insert each month

then run this query

SELECT
months.month,
MONTHNAME(flights.Datefly) AS mon,
COUNT(flights.Datefly) AS CountOfMonths
FROM
months LEFT JOIN flights ON months.month=flights.mon
GROUP BY
month

and that should give you blank months - at least i think you can left join on a calculated field.

Pigmaster

Sorry kpzani

Left join don't work.

I can get all months and years now from query but still no empty months.

Only thing I can think of is to run a query for each month in each year. I know that it will be a bigger overhead on processor time.

kpzani

if you can't join on a calculated field then make a temporary table.

and left join against that.

depending on the version of mysql you can also use sub queries

select field1 from (select field2 as field1 from table1)

check the mysql manual for info on sub queries and left joins.

Pigmaster

OK, here is the current query

SELECT month(datefly) monthnum, monthname(datefly) monthname, year(datefly) yearnum, COUNT(*) FROM flights group BY year(datefly) DESC, month(datefly) DESC

monthnum monthername yearnum count
4 April 2004 1
3 March 2004 3
1 January 2004 2

It will also deal with different month/years, eg April 2003 and April 2004 will be 2 seperate results.

kpzani

the sub query syntax would be:

SELECT months.month, tbl.mnum, tbl.ynum, tbl.noflights FROM
months LEFT JOIN (
SELECT month(datefly) AS mnum, monthname(datefly) AS mname, year(datefly) AS ynum, COUNT(datefly) AS noflights FROM flights group BY year(datefly) DESC, month(datefly) DESC ) AS tbl
ON
months.month=tbl.mname

that should work.
requires mysql 4.1 or newer

Pigmaster

Oh Yeah, works a treat.

Thanks Thanks Thanks

PS. just a small question, for the months that have no records it returns a null value, so when I stick an "order by ynum, mnum" on the end of the query it sorts correctly, apart from the null values as these appear at the top. Is it possible to stick in default values of 0 instead of Null

Thanks again for your help

kpzani

the null values are coming from the inner table this can be changed in a number of ways - lets tackle the null monthname first - change the order by to the month fields from the table that always has values in it

then make sure you are using the field month rather than the field mnum.

to ensure the noflights has a value in it you can move the count up to the outer query.
SELECT months.month, tbl.mnum, tbl.ynum, COUNT(tbl.datefly) AS noflights FROM
months LEFT JOIN (
SELECT month(datefly) AS mnum, monthname(datefly) AS mname, year(datefly) AS ynum, datefly FROM flights group BY year(datefly) DESC, month(datefly) DESC ) AS tbl
ON
months.month=tbl.mname
ORDER BY month

for the years you will need another field - I would add three fields to the month table as follows

CREATE TABLE months (
yearmonth varchar(20) primary key,
year int unsigned,
monthnumber int unsigned,
month varchar(20));

then add records like
INSERT INTO months VALUES
("2004January", 2004, 1, "January");
repeat as necessary

then change your query as follows:
SELECT months.yearmonth, months.month, months.year, COUNT(tbl.datefly) AS noflights FROM
months LEFT JOIN (
SELECT CONCAT(year(datefly, monthname(datefly)) AS ym, datefly FROM flights) AS tbl
ON
months.yearmonth=tbl.ym
GROUP BY yearmonth
ORDER BY year, monthnumber

let me know how that goes.

Pigmaster

Thanks again kpzani for replying

Originally posted by kpzani
the null values are coming from the inner table this can be changed in a number of ways - lets tackle the null monthname first - change the order by to the month fields from the table that always has values in it

SELECT months.month, tbl.mnum, tbl.ynum, tbl.noflights FROM
months LEFT JOIN (
SELECT month(datefly) AS mnum, monthname(datefly) AS mname, year(datefly) AS ynum, COUNT(datefly) AS noflights FROM flights group BY year(datefly) DESC, month(datefly) DESC ) AS tbl
ON
months.month=tbl.mname
ORDER BY month

then make sure you are using the field month rather than the field mnum.

OK, this works fine.

to ensure the noflights has a value in it you can move the count up to the outer query.
SELECT months.month, tbl.mnum, tbl.ynum, COUNT(tbl.datefly) AS noflights FROM
months LEFT JOIN (
SELECT month(datefly) AS mnum, monthname(datefly) AS mname, year(datefly) AS ynum, datefly FROM flights group BY year(datefly) DESC, month(datefly) DESC ) AS tbl
ON
months.month=tbl.mname
ORDER BY month

I get an error that there is no Group By statement for use of count()

for the years you will need another field - I would add three fields to the month table as follows

CREATE TABLE months (
yearmonth varchar(20) primary key,
year int unsigned,
monthnumber int unsigned,
month varchar(20));

then add records like
INSERT INTO months VALUES
("2004January", 2004, 1, "January");
repeat as necessary

OK, I redid the table and loaded up 1 years worth of months

then change your query as follows:
SELECT months.yearmonth, months.month, months.year, COUNT(tbl.datefly) AS noflights FROM
months LEFT JOIN (
SELECT CONCAT(year(datefly, monthname(datefly)) AS ym, datefly FROM flights) AS tbl
ON
months.yearmonth=tbl.ym
GROUP BY yearmonth
ORDER BY year, monthnumber

let me know how that goes.

I get
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' monthname(FlightDate)) AS ym, FlightDate FROM flightlog) AS tbl ON months.yearm' at line 1

Did you actually run these queries and did it work ok as I have tried but what you are quoting here is beyond my mysql abilities, so I am stuck, if you know what i mean

Pigmaster

I forgot to say that I have created a dummy table to play with called flightlog (was flights) and the date field is flightdate (was datefly) just in case you were wondering with my last post in the error message.

I got these messages from EMS MySQL query

kpzani

sorry there's a typo in the query
missing bracket after year(datefly

try this

SELECT months.yearmonth, months.month, months.year, COUNT(tbl.datefly) AS noflights FROM
months LEFT JOIN (
SELECT CONCAT(year(datefly), monthname(datefly)) AS ym, datefly FROM flights) AS tbl
ON
months.yearmonth=tbl.ym
GROUP BY yearmonth
ORDER BY year, monthnumber

No i'm not running these queries, just typing them into the post message text box - so expect lots of errors.

Pigmaster

Fantastic, brilliant, WOW

Works a treat all round now.

Thanks

Have a great Christmas and New Year