# Group by and SUM function

Thread began 11/25/2010 5:10 am by nmabey414064 | Last modified 12/17/2010 7:00 am by nmabey414064 | 2182 views | 6 replies |

## 11/25/2010 5:10 am  |  #1 nmabey414064

### Group by and SUM function

Hi,

Grouping my data by calendar month and display the sum of all the days in a month works perfectly fine.

But we have now realized that for most of our customers it would make sense to display data of their billing month which not nessessary means 1st till 30th/31st.

Is it possible to use the sum function and group by month but tell the graph that start and end day are different to 1st and 30th/31st?

Thanks very much for your help.

## 11/25/2010 8:15 am  |  #2 Ray BorduinWebAssist

What you could do in that case is subtract the necessary number of days from the date so that when you group by month it ends up being offset by their billing date.

Did this help? Tips are appreciated...

## 11/25/2010 11:18 am  |  #3 nmabey414064

In the part query that looks like this:

WADWCStats_cmd.CommandText = "SELECT LEFT(Format(Stats_Date,'mmmm'), 3)+' '+STR(DATEPART('yyyy', Stats_Date)) AS XLABEL, SUM(Minutes) AS YVALUE FROM Stats GROUP BY LEFT(Format(Stats_Date,'mmmm'), 3)+' '+STR(DATEPART('yyyy', Stats_Date)), DATEPART('yyyy', Stats_Date), DATEPART('m', Stats_Date) ORDER BY DATEPART('yyyy', Stats_Date) ASC, DATEPART('m', Stats_Date) ASC "

where could I substract the days if we say the first day of the billing period would be the 5th of a month with 30 days?

Sorry but I can't really identify which part of that code stand for the start date.

Thanks very much

## 11/29/2010 11:56 am  |  #4 Ray BorduinWebAssist

You would adjust the date field in the group by area... try:

SELECT LEFT(Format(Stats_Date,'mmmm'), 3)+' '+STR(DATEPART('yyyy', Stats_Date)) AS XLABEL, SUM(Minutes) AS YVALUE FROM Stats GROUP BY LEFT(Format(Date_Add(Stats_Date, interval -5 day),'mmmm'), 3)+' '+STR(DATEPART('yyyy', Date_Add(Stats_Date, interval -5 day))), DATEPART('yyyy', Date_Add(Stats_Date, interval -5 day)), DATEPART('m', Date_Add(Stats_Date, interval -5 day)) ORDER BY DATEPART('yyyy', Stats_Date) ASC, DATEPART('m', Stats_Date) ASC

Did this help? Tips are appreciated...

## 12/10/2010 6:39 am  |  #5 nmabey414064

Hi,
I have tried it with the code you send, but for some reasons it doesn't work. It does return the error "DynamicWebCharts/Data/diagramm_dataparser.asp?".

I don't know really where to start finding the error, as I don't know exactly what the different parts of the query mean.
Which part returns the month, year, which parts add up the days?

Do you have any idea why it doesn't work?

Thanks very much for teh help!
Jana

## 12/10/2010 10:24 am  |  #6 Ray BorduinWebAssist

It is hard to say without debugging the page directly.

Start by taking that sql statement and running it in your database directly. That will be the easiest way to make adjustments and make sure you are getting the data you need.

I didn't know what database you were using, so my syntax could be wrong for your database type... once you have your SQL statement worked out, if you still have problems you should try browsing the page referenced "DynamicWebCharts/Data/diagramm_dataparser.asp?" directly. When it shows the page name like that it means that page has an error. By viewing the page you can find out more about the precise error.

If you want Webassist to help directly you would have to sign up for a paid phone support incident. That would allow us to connect to your computer and debug the problem with you directly.

Did this help? Tips are appreciated...

## 12/17/2010 7:00 am  |  #7 nmabey414064

Yes I understand that.
It might be that we have a SUM(Minutes) in combination with WHERE in our code. It always results an error.
However to solve this, we have now used the static template and wrote a serie of queries that add all the minutes up. Afterwards they are passed to the x and y value of the chart.
It works! Maybe this helps somebody who is confronted with the same problem!

Thanks

## Build websites with a little help from your friends

Your friends over here at WebAssist! These Dreamweaver extensions will assist you in building unlimited, custom websites.

## Build websites from already-built web applications

These out-of-the-box solutions provide you proven, tested applications that can be up and running now.  Build a store, a gallery, or a web-based email solution.

## Everything else!

Close Window

Account or customer service questions?

Need technical support?

Content

Layout

Ease of use

We do not respond to comments submitted from this page directly, but we do read and analyze any feedback and will use it to help make your experience better in the future.

Close Window

## We were unable to retrieve the attached file

Close Window

### Attach and remove files

• Text Formatting
• Insert Element
• Block Formatting
• Cancel
Close Window

Close Window

#### This is how you use right click RTF editing

Enable right click RTF editing option allows you to add html markup into your tutorial such as images, bulleted lists, files and more...

-- click to close --