Solved

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

Posted on 2006-06-12
1,151 Views
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
Question by:R-U-Bn
[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
• 3
• 2

LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 250 total points
ID: 16885672
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

ID: 16892614
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 143

Expert Comment

ID: 16892851
>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

ID: 16894475
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 143

Expert Comment

ID: 16894523
>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

Question has a verified solution.

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

Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Suggested Courses
Course of the Month3 days, 12 hours left to enroll