Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1444
  • Last Modified:

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

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
R-U-Bn
Asked:
R-U-Bn
  • 3
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
R-U-BnAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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
 
R-U-BnAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now