question

JR03 avatar image
JR03 asked

How to get total revenue per month

HI there. i am quite new at SQL. I want to pull data from tables that gives me the total revenue per month. I have the number of sales done per month however i cannot seem to get the sum right for revenue total per month to fall under the columns for each month. My query gives me this outcome: ![alt text][1] where as i want the following in total revenue to fall under each month. [1]: /storage/temp/2946-untitled.jpg
sum
untitled.jpg (4.7 KiB)
4 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Can you please give us more sample data and the SQL you have tried so far? It sounds like maybe PIVOT would work, but I can't be sure without more information.
0 Likes 0 ·
Hi, I managed this and it seems to display what I need. SELECT * FROM ( SELECT left(datename(month,Close_Date),3)as [month], Actual_Revenue as Amount FROM AMGR_Opportunity_Tbl ) as s PIVOT ( SUM(Amount) FOR [month] IN (jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, dec) )AS pvt
0 Likes 0 ·
I would however like to put the data in a bar chart, however only the month values are displayed and not the amounts
0 Likes 0 ·
You might want to change the `SUM(Amount)` to `SUM(ISNULL(Amount,0))` just to rule out the possibility of months without sales getting excluded from your report. As to the bar chart - how are you doing that? SSRS? Excel? Something else?
0 Likes 0 ·

0 Answers

· Write an Answer

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.