Jump to content
Xtreme .Net Talk

Recommended Posts

Posted

Hi,

 

I'm having a slight problem with my SQL

 

I am pulling in data from | Date | Branch | Value | Dept |

 

I want to sort the information into months then sum that amount ..

 

so something like:

 

SELECT sum(Date), sum(Value), Branch, Dept FROM db

 

I have it to sum(Value) already and it works, but now I need it by months .. then by years ..

 

Any ideas?

Posted (edited)

I think it should be:

 

SELECT SUM(value) From Table GROUP BY Date HAVING Date BETWEEN 1/1/2003 AND 2/1/2003

 

or this:

 

SELECT SUM(Value) FROM Table Group by date HAVING DATEPART(month, Date)=1

Edited by Cassio
  • *Experts*
Posted

Normally you'd only do one sum through your SQL Code, like by Month as Cassio showed you. On the form (or report), you do the summing by year and/or a Grand Total. You can use the DataTable's compute method for this if you like:

object oTotal = ds.Tables[0].Compute("SUM(value)");
// Might want to check if oTotal !=null
int total = Convert.ToInt32(oTotal);

 

If you want both the Monthly and Yearly totals, you're probably best off running two queries.

 

-Ner

"I want to stand as close to the edge as I can without going over. Out on the edge you see all the kinds of things you can't see from the center." - Kurt Vonnegut
Posted

It do sound like two queries would be the best solution. Or, let the database return two result sets by selecting both at the same time. You would loose basically no speed on that.

 

CREATE PROCEDURE dbo.return_sums AS

SELECT SUM(Value) FROM Table Group by DATEPART(month, Date);

SELECT SUM(Value) FROM Table Group by DATEPART(year, Date);

GO

Posted

I'm sorry... I always work with stored procedures... you don't have to do that. The syntax I wrote is what you would use in SQL Server to create a stored procedure that you later run from your code like this: "EXEC p_return_sums".

 

But, you do not have to use stored procedures if you don't want to. Simply remove that and run:

SELECT SUM(Value) FROM Table Group by DATEPART(month, Date);

SELECT SUM(Value) FROM Table Group by DATEPART(year, Date);

 

/Ricky

Posted

so you could have a list of stored procedures and then use them when you want to connect to the SQL server and get the information...? How would they work with a data adapter connecting and stuff??

I'm still pretty new to vb.net and even newer to sql server, so sorry for my lack of knownledge .. :s

Posted

So you want this all in a single result?

 

SELECT SUM(Value) AS total, Month(Date) AS date FROM Table GROUP BY Month(Date) ORDER BY Month(Date) ASC

UNION ALL

SELECT SUM(Value) AS total, Year(Date) AS date FROM Table GROUP BY Year(Date) ORDER BY Year(Date) ASC

UNION ALL

SELECT SUM(Value) AS total, 'All' AS date FROM Table

 

Something like that anyway.

Gamer extraordinaire. Programmer wannabe.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...