Solved

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

Posted on 2006-06-12
5
1,065 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
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 142

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 142

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MySQL Error 3 43
Multilanguage Database Design in MySQL 5 112
Best way to find a random number in a list of a million numbers 11 51
PHP: concatenate query 13 68
A lot of articles have been written on splitting mysqldump and grabbing the required tables. A long while back, when Shlomi (http://code.openark.org/blog/mysql/on-restoring-a-single-table-from-mysqldump) had suggested a “sed” way, I actually shell …
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://…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

777 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