order by month

[andydeans]

Hi,

been looking all night on net but cannot find ORDER BY Month anywhere.

This is my query:

SELECT MonthPaid AS XLABEL, SUM(Amount) AS YVALUE
FROM monthlycommission
WHERE YearPaid= NOW( )
GROUP BY MonthPaid

However when i display this it goes June, March then May

I want to add an order by statement so it will order March, May June and so on.

help appreciated.

thanks

[andydeans]

tried

order by MonthPaid

but no joy, also Tried

ORDER BY 'MonthName'

and

ORDER BY Month Paid = 'MonthName'

[griswolf]

Most times there is a datetime or date column when you need to keep track of when something happens. It is almost never a good idea to store the month, particularly as some char type. You can order by or group by month(date or datetime column)

[andydeans]

i was following tesu's instructions from previous post to get month and i now want to an order by.

i created a view like he said and wanted to be able to order by month.

is it not possible then?

i manage to use a where clause on the YearPaid is NOW()
which is on the same view and querying the same date column so not sure why it wont work?

what is proper code to use for order by month correctly?

thanks

[griswolf]

what is proper code to use for order by month correctly?

... order by month(date_column)... but you probably don't want that: Why bother with month when you can order by date_column which will not only order by the month but also by the day or millisecond (if it is a datetime). Group by also works this way, and for group by, using the month function makes much more sense.

You should learn to use the mysql docs here: http://dev.mysql.com/doc/refman/5.0/en/

I learned almost everything I know about sql and mysql by reading these docs and looking at some examples written by an expert. I expect you could do so as well.

[andydeans]

I have read through some examples and googled around, that how i got the examples i tried and failed.

the reason i need it by month is that what the end user wans to display. it needs to show the months for the year so i added in the Where Year statement myself which works and i get the results i want but when i display on page it displays in no particular order.

the column is a date column in the database as that is how i maanged to do the WHERE clause on it, same column.

the labels are to display it in a flash chart.

thanks

[andydeans]

Hi griswolf or anyone out there, i have decided if it is not working it must be the View that it does not like.

i have tried to replicate the view but instead as just a select query. however getting problems, main columns is i need to have the total of the amount/commission columns display along with the datepaid and the year and month which i have added.

query so far:

SELECT c.ClientID, c.App1FirstName, c.App1LastName, u.FirstName, u.LastName, MONTHNAME(DatePaidGI) as MonthDue, YEAR(DatePaidGI) as YearPaid, p.AmountGI AS Total, DatePaidGI  FROM (clients c JOIN users u on c.ClientUserID = u.UserID) JOIN generalinsurance p on c.ClientID = p.clients_ClientID     WHERE DatePaidGI IS not NULL     
UNION  SELECT c.ClientID, c.App1FirstName, c.App1LastName, u.FirstName, u.LastName, MONTHNAME(DatePaid) as MonthPaid, YEAR(DatePaid) as YearPaid, sum(p.amount) as sub_amt, DatePaid 
FROM (clients c JOIN users u on c.ClientUserID = u.UserID) JOIN protection p on c.ClientID = p.clients_ClientID     WHERE DatePaid IS not NULL     
UNION  SELECT c.ClientID, c.App1FirstName, c.App1LastName, u.FirstName, u.LastName, MONTHNAME(DatePaid) as MonthPaid, YEAR(DatePaid) as YearPaid, sum(p.amount) as sub_amt, DatePaid    
FROM (clients c JOIN users u on c.ClientUserID = u.UserID) JOIN buytolet p on c.ClientID = p.clients_ClientID     WHERE DatePaid IS not NULL     
UNION  SELECT c.ClientID, c.App1FirstName, c.App1LastName, u.FirstName, u.LastName, MONTHNAME(DatePaid) as MonthPaid, YEAR(DatePaid) as YearPaid, sum(p.amount) as sub_amt, DatePaid    
FROM (clients c JOIN users u on c.ClientUserID = u.UserID) JOIN mortgage p on c.ClientID = p.clients_ClientID     WHERE DatePaid IS not NULL     
UNION  SELECT  c.ClientID, c.App1FirstName, c.App1LastName, u.FirstName, u.LastName, MONTHNAME(DatePaidReferral) as MonthPaid, YEAR(DatePaidReferral) as YearPaid, sum(p.Commission) as sub_amt, DatePaidReferral    
FROM (clients c JOIN users u on c.ClientUserID = u.UserID) JOIN referrals p on c.ClientID = p.clients_ClientID     WHERE DatePaidReferral IS not NULL     
UNION  SELECT c.ClientID, c.App1FirstName, c.App1LastName, u.FirstName, u.LastName, MONTHNAME(DatePaidOverseas) as MonthPaid, YEAR(DatePaidOverseas) as YearPaid, sum(p.Commission) as sub_amt, DatePaidOverseas    
FROM (clients c JOIN users u on c.ClientUserID = u.UserID) JOIN overseas p on c.ClientID = p.clients_ClientID     WHERE DatePaidOverseas IS not NULL      
UNION  SELECT c.ClientID, c.App1FirstName, c.App1LastName, u.FirstName, u.LastName, MONTHNAME(DatePaid) as MonthPaid, YEAR(DatePaid) as YearPaid, sum(p.Commission) as sub_amt, DatePaid    
FROM (clients c JOIN users u on c.ClientUserID = u.UserID) JOIN debtmanagement p on c.ClientID = p.clients_ClientID     WHERE DatePaid IS not NULL      
 UNION  SELECT c.ClientID, c.App1FirstName, c.App1LastName, u.FirstName, u.LastName, MONTHNAME(DateCommissionReceived) as MonthPaid, YEAR(DateCommissionReceived) as YearPaid, sum(p.Commission) as sub_amt, DateCommissionReceived    
FROM (clients c JOIN users u on c.ClientUserID = u.UserID) JOIN pensions_investments p on c.ClientID = p.clients_ClientID     WHERE DateCommissionReceived IS not NULL 
ORDER BY DatePaidGI       
;

Can anyone help where i am going wrong? i this i need to declare AS at the start for the columns but i tried that with brackets around the columns like in the view statement but never worked.

hoppe you can help.

thanks

[andydeans]

hi guys,

is ther anyone who can pitch in and see where i am going wrong with query?

struggling for a deadline today.

thanks again for all your great help.

[tesuji]

Hi andydeans,

still having problems?

Each select statement is incorrect, for example:

...
UNION SELECT c.ClientID, c.App1FirstName, c.App1LastName, u.FirstName, u.LastName, MONTHNAME(DatePaid) as MonthPaid, YEAR(DatePaid) as YearPaid, [B][U]sum(p.amount)[/U][/B] as sub_amt, DatePaid FROM (clients c JOIN users u on c.ClientUserID = u.UserID) JOIN mortgage p on c.ClientID = p.clients_ClientID WHERE DatePaid IS NOT NULL
...

Because of the aggregate function sum(...) a GROUP BY clause is absolutely necassary, where each column not appearing in aggregate function must be contained. Therefore you have to add

GROUP BY c.ClientID, c.App1FirstName, c.App1LastName, u.FirstName, u.LastName, MonthPaid, YearPaid

to the above SELECT and similar GROUP BY clauses to the other selects.

I did already express the necessity of GROUP BY several times if a select statement contains aggregate functions. You should carefully read and understand the example I showed in your other thread as somebody asked why that ORDER BY would have been that necessity.

Important: If a GROUP BY is necessary because of aggregate functions you must carefully decide which other columns should be listed in the select statement. If you choose too much, for example all columns of a table there wouldn't be any useful data to group by and therefore to sum up. In other words, the sum then only contains the single value of each row.

For example, you decide to sum up all sales amounts obtained per month: select product, month(date) as Month, sum (salesamount) from ... group by product, month order by month; This sums up all sales amounts per month. That is kind of doing a monthly settlement.

If you decide also to add the day(date) to your select statement you can write: select product, month(date) as Month, day(date) as Day, sum (salesamount) from ... group by product, Month, Day order by Month, Day;

This does a daily settlement which is not that often required.

Again, please read my example given in your older thread.

-- tesu

[tesuji]

Here is an example on how to group and order by year and month.

select year(orderDate) as Year, month(orderDate) as Month, 
 sum(quantity) as Quantity, sum(quantity*ItemPrize) as "Toal per Month" 
   from orderline join customerorder group by Year, Month order by Year, Month;

-- Result
/*
Year  Month  Quantity  Toal per Month
-------------------------------------
2009  10     36         7946.94
2009  11      3         2248.00
2009  12     32        17231.83
2010   1      3          192.00
2010   2      9         1365.95

Please consider that this result has been selected from a Sybase database. So possibly some details of the select need to be adjusted.
*/

If you decide to add a further column, for example say the item price, your above compact monthly settlement would be destroyed because itemprice must appear in group by clause.

I hope this example will be a little help.

Addition: FROM orderline JOIN customerorder is modern form of FROM orderline o, customerorder c where c.orderID = o.orderID. This only works if all primary and foreign keys are set up correctly.

-- tesu

[andydeans]

Hi tesu,

thanks for that, as mentioned i have tried to use your examples, problem is i need to display where duedate = this year now.

i add that into my select statement and it returns no results, yet i take it out i get the results, but ones from maybe next year also.

if i add a group by clasue to each select statement do you reckon it will work or is any other of my syntax wrong?

thansk tesu

[andydeans]

hi tesu,

i created a view like your examples, and where i am struggling is querying it to use with a flash chart:

SELECT MonthPaid AS XLABEL, SUM(Amount) AS YVALUEFROM monthlycommissionWHERE YearPaid= NOW( )GROUP BY MonthPaid

i want to order by month in the proper order but cannot find a solution.

then i started to write that union select query to query the tables rather than using the view.

can you help? is it possible to order by month in correct order when querying a view like my code above?

thanks again

[tesuji]

hi

if you want to select rows where duedate is from current year, you should extend where clause: WHERE ... AND YEAR(DatePaid) = YEAR(CURDATE()).

I am about to examine your select statements more exact this evening (just during watching world football championship). I have already got the funny feeling that something is going wrong concerning the logical insight of what aggregate functions are and how to use them practically. I'll answer if here is somewhat more clarity.

-- tesu

[drjohn]

order by is ordering it in alphabetical order in your opening post.

that's why you need a different approach, using the date data type and then getting at the year and month. (which are stored as numbers)

[andydeans]

thanks guys,

not sure what you mean though use the date type as the original table where the due date comes from in the view is a date column, so i must be the view that is causing the issue?

if so how can i change that view to just a query which queries all the date columns like i tried at start of thread?

many thanks

[tesuji]

Hi andydeans,

there seems to be a lot of problems, kind of going round in circles. I personally would suggest that we should go these problems step by step. In each step we shall handle a small and manageable problem. To each step I'll make a suggestion of sql code. Then it will be your task to get that code running on your database. If errors occur, you must post firstly the ORIGINAL sql statement which caused the error and secondly the complete error message. If and only if a current problem is solved we proceed to next problem.

Possibly we shall go back to the UNION problem, where we should start from with a very simple example. I suggest that we start from the below reduced statements, which are the original sqls posted by you some days ago:

SELECT c.ClientID, c.App1FirstName, c.App1LastName, u.FirstName, u.LastName, MONTHNAME(DatePaid) as MonthPaid, YEAR(DatePaid) as YearPaid, sum(p.amount) as sub_amt, DatePaid    
FROM (clients c JOIN users u on c.ClientUserID = u.UserID) JOIN buytolet p on c.ClientID = p.clients_ClientID     WHERE DatePaid IS not NULL     

UNION  SELECT c.ClientID, c.App1FirstName, c.App1LastName, u.FirstName, u.LastName, MONTHNAME(DatePaid) as MonthPaid, YEAR(DatePaid) as YearPaid, sum(p.amount) as sub_amt, DatePaid    
FROM (clients c JOIN users u on c.ClientUserID = u.UserID) JOIN mortgage p on c.ClientID = p.clients_ClientID     WHERE DatePaid IS not NULL

If this problem is already solved or become obsolete, you could suggest another small problem we can start from. In this case you should post the complete sql code.

Ok, there is something essentials missing also the sql contains some unnecesssary stuff. Let me do first changes:

SELECT YEAR(DatePaid) as YearPaid, MONTH(DatePaid) as MonthPaid, 
 c.ClientID, c.App1FirstName, c.App1LastName, u.FirstName, u.LastName, sum(p.amount) as sub_amt,
  FROM clients c JOIN users u on c.ClientUserID = u.UserID 
    JOIN buytolet p on c.ClientID = p.clients_ClientID
      WHERE DatePaid IS not NULL  AND YearPaid = YEAR(CURDATE()) 
	GROUP BY YearPaid, MonthPaid, c.ClientID, c.App1FirstName, 
	  c.App1LastName, u.FirstName, u.LastName 
             ORDER BY MonthPaid
UNION  
SELECT YEAR(DatePaid) as YearPaid, MONTH(DatePaid) as MonthPaid, 
 c.ClientID, c.App1FirstName, c.App1LastName, u.FirstName, u.LastName, sum(p.amount) as sub_amt,
  FROM clients c JOIN users u on c.ClientUserID = u.UserID 
   JOIN mortgage p on c.ClientID = p.clients_ClientID
     WHERE DatePaid IS not NULL AND YearPaid = YEAR(CURDATE())      
       GROUP BY YearPaid, MonthPaid, c.ClientID, c.App1FirstName, 
	 c.App1LastName, u.FirstName, u.LastName 
            ORDER BY MonthPaid;

Now it's your task to get this sql code running on your database. Ok, I didn't test it so some (minor) changes are obvious. We will not proceed nor extend the code towards new wishes until it is faultless and you are pleased about the output. If any error occurs you can't solve it instantly, please post your ORIGINAL erroneous sql statement and the COMPLETE error message.

You should consider that summation by sum(p.amount) will only be done for rows which contains the same values for these columns: (YearPaid, MonthPaid, c.ClientID, c.App1FirstName, c.App1LastName, u.FirstName, u.LastName), except of p.amount what will be summed up. If only one value differs, sum(p.amount) is reset to zero and restarts again. That means that you might have put too much columns in the query.

So now start and let me know your results asap (I will be online for the next 8 hours)

-- tesu

[andydeans]

Hi Tesu,

thank you for your help, i started working away over weekend and this morning and think i have solved it the isse, but maybe it needs modified again if you think it is not right.

First i took your create view code you gave me last week and modified it and here is what i have come up with:

CREATE OR REPLACE VIEW monthlycommission( ProductID, Product, ClientID, ClientFirstName, ClientLastName, Firstname, Lastname, MonthPaid, YearPaid, DatePaid, Amount) AS  SELECT GeneralInsuranceID, 'generalinsurance', c.ClientID, c.App1FirstName, c.App1LastName, u.FirstName, u.LastName, MONTHNAME(DatePaidGI) as MonthDue, YEAR(DatePaidGI) as YearPaid, DatePaidGI, p.AmountGI    
FROM (clients c JOIN users u on c.ClientUserID = u.UserID) JOIN generalinsurance p on c.ClientID = p.clients_ClientID     WHERE DatePaidGI IS not NULL     
UNION  SELECT ProtectionID, 'protection', c.ClientID, c.App1FirstName, c.App1LastName, u.FirstName, u.LastName, MONTHNAME(DatePaid) as MonthPaid, YEAR(DatePaid) as YearPaid, DatePaid, p.amount 
FROM (clients c JOIN users u on c.ClientUserID = u.UserID) JOIN protection p on c.ClientID = p.clients_ClientID     WHERE DatePaid IS not NULL     
UNION  SELECT BuyToLetID, 'buytolet', c.ClientID, c.App1FirstName, c.App1LastName, u.FirstName, u.LastName, MONTHNAME(DatePaid) as MonthPaid, YEAR(DatePaid) as YearPaid, DatePaid, p.amount    
FROM (clients c JOIN users u on c.ClientUserID = u.UserID) JOIN buytolet p on c.ClientID = p.clients_ClientID     WHERE DatePaid IS not NULL     
UNION  SELECT MortgageID, 'mortgage', c.ClientID, c.App1FirstName, c.App1LastName, u.FirstName, u.LastName, MONTHNAME(DatePaid) as MonthPaid, YEAR(DatePaid) as YearPaid, DatePaid, p.amount    
FROM (clients c JOIN users u on c.ClientUserID = u.UserID) JOIN mortgage p on c.ClientID = p.clients_ClientID     WHERE DatePaid IS not NULL     
UNION  SELECT ReferralID, 'referrals', c.ClientID, c.App1FirstName, c.App1LastName, u.FirstName, u.LastName, MONTHNAME(DatePaidReferral) as MonthPaid, YEAR(DatePaidReferral) as YearPaid, DatePaidReferral, p.Commission    
FROM (clients c JOIN users u on c.ClientUserID = u.UserID) JOIN referrals p on c.ClientID = p.clients_ClientID     WHERE DatePaidReferral IS not NULL     
UNION  SELECT OverseasID, 'overseas', c.ClientID, c.App1FirstName, c.App1LastName, u.FirstName, u.LastName, MONTHNAME(DatePaidOverseas) as MonthPaid, YEAR(DatePaidOverseas) as YearPaid, DatePaidOverseas, p.Commission    
FROM (clients c JOIN users u on c.ClientUserID = u.UserID) JOIN overseas p on c.ClientID = p.clients_ClientID     WHERE DatePaidOverseas IS not NULL      
UNION  SELECT DebtID, 'debtmanagement', c.ClientID, c.App1FirstName, c.App1LastName, u.FirstName, u.LastName, MONTHNAME(DatePaid) as MonthPaid, YEAR(DatePaid) as YearPaid, DatePaid, p.Commission    
FROM (clients c JOIN users u on c.ClientUserID = u.UserID) JOIN debtmanagement p on c.ClientID = p.clients_ClientID     WHERE DatePaid IS not NULL      
 UNION  SELECT InvestmentID, 'pensions_investments', c.ClientID, c.App1FirstName, c.App1LastName, u.FirstName, u.LastName, MONTHNAME(DateCommissionReceived) as MonthPaid, YEAR(DateCommissionReceived) as YearPaid, DateCommissionReceived, p.Commission    
FROM (clients c JOIN users u on c.ClientUserID = u.UserID) JOIN pensions_investments p on c.ClientID = p.clients_ClientID     WHERE DateCommissionReceived IS not NULL      
;

I then created this query to the view to allow me to create the graph i was having trouble with implimenting the months in the right order:

SELECT MonthPaid AS XLABEL, SUM(Amount) AS YVALUE
FROM monthlycommission
WHERE YearPaid= NOW( )
GROUP BY MonthPaid
ORDER BY DatePaid

this seems to be working but maybe it is not right?

i still would like to be able to do a select like you have given on another table so that will still be useful to work with if that is ok?

do you think the above code is fine?

another thing Tesu, what is difference with me using a veiw and querying rather than using a long complex SELECT query instead?

thank you so so much again.

[tesuji]

i created a view like your examples, and where i am struggling is querying it to use with a flash chart:

SELECT MonthPaid AS XLABEL, SUM(Amount) AS YVALUEFROM monthlycommissionWHERE YearPaid= NOW( )GROUP BY MonthPaid

i want to order by month in the proper order but cannot find a solution.

Scanning your posts I found:

YearPaid= NOW( ) --> always false for YearPaid is int (2010, 2009 etc) and NOW() is date (2010-06-20). So above query always gives empty result set. Btw, your database should report an error because of incompatible data types.

order by month:
supposing Month and Year can be gotten from DatePaid simply add order by clause:

SELECT YEAR(DatePaid) as cyear, MONTH(DatePaid) AS XLABEL, SUM(Amount) AS YVALUE  FROM monthlycommission
 WHERE cyear = YEAR(NOW()) GROUP BY cyear, XLABEL ORDER BY XLABEL

You can't omit cyear from GROUP BY clause. If you want to drop cyear from output list, you could do a second query on above query:

Select XLABEL, YVALUE FROM
 (SELECT DatePaid, MONTH(DatePaid) AS XLABEL, SUM(Amount) AS YVALUE  FROM monthlycommission
    WHERE YearPaid= YEAR(NOW()) GROUP BY XLABEL) AS must_have_a_name
      ORDER BY XLABEL;

Don't get distract by this interjection...

-- tesu

[tesuji]

I then created this query to the view to allow me to create the graph i was having trouble with implimenting the months in the right order:

SELECT MonthPaid AS XLABEL, SUM(Amount) AS YVALUE
FROM monthlycommission
WHERE YearPaid= NOW( )
GROUP BY MonthPaid
ORDER BY DatePaid

this seems to be working but maybe it is not right?

Can hardly be correct. Carefully see my last post just above and compare your code with mine!

Very important to me: please, tell me whether this quoted code, your code, would really run on MySQL without any error messages!

another thing Tesu, what is difference with me using a veiw and querying rather than using a long complex SELECT query instead?

The only difference is that a view can be used everywhere without copying its code, also the selects working on views look somewhat more handier.

A further difference exists if we consider materialized views managing a huge amount of data.

Drawback of views: There are very restrictive rules for updating columns of a view.

I am glad if you progress ...

-- tesu

[andydeans]

hi tesu,

is the year paid though not using the Date column so NOW ( ) should be fine as it is not an INT ?

thanks again

[andydeans]

thanks, glad i am learning from the master :)

it throws no erros at all, all works fine when i run on mysql database.

should there be errors?

i ran the create view via php my admin and all was fine. executed query with no errors at all.

thanks again

[andydeans]

DatePaid is a date column type in all the related tables in the query.

so when i do AS YearPaid = NOW ( ) it seems to work.

what you think?

thanks again

[tesuji]

...MONTHNAME(DatePaid) as MonthPaid, YEAR(DatePaid) as YearPaid, DatePaid...

As griswolf already stated mostly it isn't a good idea to disassemble a date in year, month, day and storing these parts in a table.

You should only store DatePaid in the view. YEAR(DatePaid) gives then INT (1..12),
MONTH(DatePaid) gives then INT (28,29,30,31).

Atention: MONTHNAME(DatePaid) returns a STRING ! ('January', 'February' ...) which is not useful in ORDER BY for its alphabetical order! ----> MONTH(DatePaid) is almost better.

DatePaid is a date column type in all the related tables in the query.
so when i do AS YearPaid = NOW ( ) it seems to work.
what you think?

Sorry, but YearPaid IS NOT DatePaid !

-- tesu

[andydeans]

hi tesu,

thanks for your help.

i will change it and see hoe it goes.

strange why it is working?

ill change though and let you know how i get on.

thanks again