close ad
Databridge V2 with MySQLi support IS Now Available!
open ad
View Menu

Technical Support Forums

Free, outstanding support from WebAssist and your colleagues

rating

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 | 2049 views | 6 replies |

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.

Sign in to reply to this post

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.

Sign in to reply to this post

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

Sign in to reply to this post

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

Sign in to reply to this post

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

Sign in to reply to this post

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.

Sign in to reply to this post

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

Sign in to reply to this post

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.

Want your website pre-built and hosted?

Close Windowclose

Rate your experience or provide feedback on this page

Account or customer service questions?
Please user our contact form.

Need technical support?
Please visit support to ask a question

Content

rating

Layout

rating

Ease of use

rating

security code refresh image

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 Windowclose

We were unable to retrieve the attached file

Close Windowclose

Attach and remove files

add attachmentAdd attachment
Close Windowclose

Enter the URL you would like to link to in your post

Close Windowclose

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 --

Uploading file...