Improve company productivity with a Business Account.Sign Up

x
?
Solved

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

Posted on 2006-06-12
5
Medium Priority
?
1,614 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 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1000 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

by:R-U-Bn
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

by:Guy Hengel [angelIII / a3]
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

by:R-U-Bn
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

by:Guy Hengel [angelIII / a3]
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
The title says it all. Writing any type of PHP Application or API code that provides high throughput, while under a heavy load, seems to be an arcane art form (Black Magic). This article aims to provide some general guidelines for producing this typ…
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…

595 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