Solved

How to query:  All values per month, including 0 for empty months ?

Posted on 2006-06-12
5
1,020 Views
Last Modified: 2008-01-09
In other words,

Suppose I have a table with columns month and amount.  How can I do a query so that I can get an average or sum (anyway, a groub by), but also given a row for months that have no amount at all??

e.g.
amount      5 6 7 3 9 20 40 3
month        2 3 4 3 4 5   6  7
Now, this: SELECT month, SUM(amount) GROUP BY month, would give me...
month    2 3 4   5   6   7
SUM      5 9 16 20 40  3
.., but I want:
month   1 2 3  4   5   6   7 8 9 10 11 12
SUM      0 5 9 16 20 40  3 0 0  0   0   0

Anyone any experience in this ?  Thanks!
0
Comment
Question by:R-U-Bn
  • 3
  • 2
5 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
Comment Utility
SELECT l.Month, coalesce(g.sum_amount,0) as sum_amount
FROM (
select 1 as month UNION ALL select 2 UNION ALL select 3 UNION ALL select 4 UNION ALL select 5   UNION ALL select 6
union all select 7 UNION ALL select 8 UNION ALL select 9 UNION ALL select 10 UNION ALL select 11  UNION ALL select 12
) as l
LEFT JOIN (
SELECT month, SUM(amount)
GROUP BY month
) as s
ON s.month = l.month

0
 
LVL 2

Author Comment

by:R-U-Bn
Comment Utility
Thanks, angelIII.  That's the solution I need, but...
Isn't there a cleaner way ?   (I mean in stead of all the UNIONS and select's, isn't there a way with SET's or so, or, in general, any simpler way to get those 12 month numbers back as rows ?

Also: Aren't all these UNIONs a bit bad for performance ?

(Thanks again!)

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
>Isn't there a cleaner way ?
of course, instead of building the list of month values, you can store them once for all in a table, and use that list instead.


SELECT l.Month, coalesce(g.sum_amount,0) as sum_amount
FROM monht_list_table l
LEFT JOIN (
SELECT month, SUM(amount)
GROUP BY month
) as s
ON s.month = l.month

>Also: Aren't all these UNIONs a bit bad for performance ?
no, as that are only hard-coded rows. all in memory.

0
 
LVL 2

Author Comment

by:R-U-Bn
Comment Utility
OK, I see.

Allow me to comment my surprise:
I just find it strange that for something that I believe to be a very common query, such a long "subquery" needs to made. Although it's  not too long right now, but suppose we were not dealing with months, but something that goes, say, upto 100 in stead of 12.  It strikes me that you will then have to make a table, just for counting from 1 to 100.  What a waste.
Don't you think mysql should provide a possibility/function* to overcome this ?  You surely have come accross this kind of problem before as an expert, haven't you ?
(*I could imagine a function called TO_VERTICAL which would put 1 row with colums or just a comma seperated string or similar into several rows, this combined with some countto(100) function would do the trick)

Are you confident that there really no possibility/function for that in mysql ?  (No offense meant!!)

Forgive me for nagging, just a perfectionist, me.  I might use this solution for the future too, you see.  I hope you don't mind too much.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
>I just find it strange that for something that I believe to be a very common query
I basically agree with you, but this is not the job of the database engine.

In Oracle, the "workaround" would be to create a function with a pipelined output.
in SQL Server 2005, a function with table result would be the answer

>Are you confident that there really no possibility/function for that in mysql ?  (No offense meant!!)
well, I'm not that smart in Mysql, so I might be missing something, but still quite confident as I know this kind of limitations in the other database products.

>Forgive me for nagging, just a perfectionist, me.  I might use this solution for the future too, you see.  I hope you don't mind too much.
no offense taken, I don't mind. often enough in the same situation (other problems)

0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Creating and Managing Databases with phpMyAdmin in cPanel.
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…
This video discusses moving either the default database or any database to a new volume.

743 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

13 Experts available now in Live!

Get 1:1 Help Now