?
Solved

MySQL Monthly SUM totals

Posted on 2003-03-22
19
Medium Priority
?
1,167 Views
Last Modified: 2008-01-09
EE,
I would like to SUM a column (ordertot) from a table (select2) by month (date). The format of the date column is yyyy mm dd (ie. 2003-03-22)
So far I can display every entry, but I would like to group them by month.
I have used thus far:

select SUM(ordertot) from select2 GROUP BY Date,'yyyy mm dd'

I am unable to get the totals in a monthly format. Everything I try gives me a MySQL error.
Any help would be greatly appreciated.
dresdena1


0
Comment
Question by:dresdena1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 5
  • +1
19 Comments
 
LVL 15

Expert Comment

by:VGR
ID: 8186470
may-be that easy :

mysql> select left(thedate,7),sum(ordertot) from select2 group by left(thedate,7);
+-----------------+---------------+
| left(thedate,7) | sum(ordertot) |
+-----------------+---------------+
| 2003-01         |             9 |
| 2003-02         |             3 |
| 2003-03         |             1 |
+-----------------+---------------+
3 rows in set (0.00 sec)

from this test table :
mysql> create table select2 (id integer unique auto_increment,ordertot integer,thedate date);

mysql> select * from select2;
+----+----------+------------+
| id | ordertot | thedate    |
+----+----------+------------+
|  1 |        1 | 2003-03-22 |
|  2 |        1 | 2003-02-22 |
|  3 |        2 | 2003-02-18 |
|  4 |        4 | 2003-01-18 |
|  5 |        5 | 2003-01-01 |
+----+----------+------------+

as you can see, the monthly results should be 01/03 : 9, 02/03 : 3 and 03/03 : 1

that's it.

regards
0
 
LVL 15

Expert Comment

by:VGR
ID: 8186473
for convenience reasons, of course write this query in your front-end script :

select left(thedate,7) as month,sum(ordertot) as value from select2 group by left(thedate,7);
0
 

Author Comment

by:dresdena1
ID: 8186497
VGR,
Thanks for such a quick response. I entered:

select left(thedate,7),sum(ordertot) from select2 group by left(thedate,7);

And I got the following error message:

MySQL said: You have an error in your SQL syntax near 'left(thedate,7);' at line 1

Any idea why?
Thanks.
dresdena1
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 15

Expert Comment

by:VGR
ID: 8186754
'thedate' was MY column name for the date
you have an other...

if it's named 'date' then do "left(date,7)"
0
 

Author Comment

by:dresdena1
ID: 8188814
VGR,
I don't know why this won't work. The field name is date.
I entered:
select left(date,7),sum(ordertot) from select2 group by left(date,7);

I get the following error message:
MySQL said: You have an error in your SQL syntax near 'left(date,7);' at line 1

I have no idea why it won't work.
Any ideas?
Thanks.
dresdena1


0
 
LVL 15

Expert Comment

by:VGR
ID: 8189128
well , then only difference I see is the name of the date field. Try to name it something else than 'date'. Surprising. It works for me.
0
 
LVL 1

Accepted Solution

by:
mtrimpe earned 600 total points
ID: 8190084
Just to recap what has been said here, and hopefully to provide a final answer.

First off ... since we're using MySQL don't use left. This will invoke a string operation on a field that is basically a signed integer (number of seconds since 1970 if i'm correct.)
The first approach was thus correct, using the MONTH() function.

When it comes to the problem why
select left(date,7),sum(ordertot) from select2 group by left(date,7);
won't work you have to imagine this query from a MySQL perspective. MySQL sees two _separate_ operations left(...). MySQL (usually, since it does seem to work for someone) doesn't notice the fact that they are equal. This it converts this query to the following pseudo-SQL command:
SELECT expr1, sum(xx) from xx group by expr2 ;
And since those aren't equal MySQL won't let you pass.

The final, right, query is:
SELECT MONTH(Date) AS SumMonth, SUM(ordertot) FROM select2 GROUP BY SumMonth ;

Hope this helps.
0
 
LVL 15

Expert Comment

by:VGR
ID: 8190309
heu... huh ?

Excuse-me, but it does work as the left(date,7) expression applies to the current row.

mysql> select left(thedate,7),sum(ordertot) from select2 group by left(thedate,7);
+-----------------+---------------+
| left(thedate,7) | sum(ordertot) |
+-----------------+---------------+
| 2003-01         |             9 |
| 2003-02         |             3 |
| 2003-03         |             1 |
+-----------------+---------------+
3 rows in set (0.00 sec)

from this test table :
mysql> create table select2 (id integer unique auto_increment,ordertot integer,thedate date);

mysql> select * from select2;
+----+----------+------------+
| id | ordertot | thedate    |
+----+----------+------------+
|  1 |        1 | 2003-03-22 |
|  2 |        1 | 2003-02-22 |
|  3 |        2 | 2003-02-18 |
|  4 |        4 | 2003-01-18 |
|  5 |        5 | 2003-01-01 |
+----+----------+------------+

as you can see, the monthly results should be 01/03 : 9, 02/03 : 3 and 03/03 : 1

that's it.

0
 
LVL 1

Expert Comment

by:mtrimpe
ID: 8190328
I wasn't saying that it didn't work for you.
All i said is that it was technically incorrect according to SQL standards. I alreadyexplained the reasoning behind it.

Apparently some SQL databases do allow this, and check for it specifically. Oracle and probably several others don't allow this.
0
 
LVL 15

Expert Comment

by:VGR
ID: 8190416
I don't why they shouldn't. It seems to me it does conform to SQL92, but I may be wrong

My best ansswer is this : it works, so it's allowed 8-)
0
 

Author Comment

by:dresdena1
ID: 8191394
mtrimpe,
Thanks very much for the answer it worked fine.

Is there a way to break up the monthly totals (by year)? The answer is numbered 1-12, but when I use it with a table that has more than 1 year's worth of entries, it adds up the related months (ie. Jan 02 + Jan 03).

If I should post another question with points I will be happy to. I care most about getting the answer.
Thanks again. dresdena1

VGR,
Thank you for all of your help. I really appreciate it, but I was just not able to get it to work.
Thanks again.
dresdena1
0
 
LVL 1

Expert Comment

by:mtrimpe
ID: 8191703
You don't need a new question ... I'll give you that one for free.

You basically have two options.
1. You can turn the SumMonth into a String instead of a Integer. Then it would look like 'mm-yyyy' or something like that. That would need this query :
SELECT
  CONCATENATE( YEAR(Date), '-', MONTH(Date) ) AS SumMonth,
  SUM(ordertot)
FROM select2
GROUP BY
SumMonth ;

BUT, this will involve string comparisons and is thus somewhat slower.

2. This is basically the best, might not be what you're looking for though. Basically you split it up into two columns, Month _and_ Year. This would give the following new SQL query:
SELECT
  YEAR(Date) AS SumYear,
  MONTH(Date) AS SumMonth,
  SUM(ordertot)
FROM select2
GROUP BY
SumYear, SumMonth ;
0
 
LVL 1

Expert Comment

by:mtrimpe
ID: 8191706
OH SORRY ....

CONCATENATE( should be CONCAT(

got a little carried away there.
0
 

Author Comment

by:dresdena1
ID: 8192101
mtrimpe,
Perfect!!!
I used your first option. It was right on the money.
You have no idea how much time this will save me.

Thanks very much!
dresdena1
0
 
LVL 1

Expert Comment

by:mtrimpe
ID: 8192251
You're welcome.

Feel free to let me know if you ever know someone who could use my services.
0
 

Author Comment

by:dresdena1
ID: 8202045
mtrimpe,
Absolutely.

Thanks again.
dresdena1
0
 
LVL 4

Expert Comment

by:bragn
ID: 8331874
Hi dresdena1.

You should look at this link:
http://www.mysql.com/doc/en/Date_and_time_functions.html

MySql has many powerful date and time functions.  For instance:

SELECT DATE_FORMAT(date,'%Y-%m'), SUM(ordertot)
FROM select2
GROUP BY DATE_FORMAT(date,'%Y-%m')

or

SELECT YEAR(date), MONTH(date), SUM(ordertot)
FROM select2
GROUP BY YEAR(date), MONTH(date)


Regards,
bragn.
0
 

Author Comment

by:dresdena1
ID: 8333188
Thanks very much,
dresdena1
0
 
LVL 15

Expert Comment

by:VGR
ID: 8335209
thanks very much dresdena1,
VGR
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question