Solved

Select statement for statistical use

Posted on 2011-09-08
2
264 Views
Last Modified: 2012-05-12
I have a table with two rows, a date and a number:

2011-01-01, 45
2011-03-01, 50
2011-03-01, 55

The output I need is:

January, 45
February, 0
Mars, 105

Closest i get is with this select:
SELECT MONTHNAME(date), SUM(number) FROM tableA

GROUP BY MONTH(date)

This gives me:

January, 45
Mars, 105

February is missing - and i need it to feed a diagram.
0
Comment
Question by:Sunsales
2 Comments
 
LVL 15

Accepted Solution

by:
Minh Võ Công earned 500 total points
Comment Utility
CREATE PROCEDURE DateList()
BEGIN
  DECLARE v1 INT DEFAULT 1;
 WHILE v1 <13 DO
 SELECT MONTHNAME(date), SUM(number) FROM tableA
 where MONTH(data) = v1
 GROUP BY MONTH(date)
 v1 = v1 +1;
  END WHILE;
END;
0
 
LVL 10

Expert Comment

by:OnALearningCurve
Comment Utility
Hi Sunsales,,

I might be missing something here but the examples you give seem to be correct.

In your sample table data you have two entries for the month of March

2011-03-01, 50
2011-03-01, 55

and your result set shows a total of 105 for March.

Surely the fix would be to edit the source data?

Hope this make sense,

Mark.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

763 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now