Illusion Posted September 25, 2003 Posted September 25, 2003 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? Quote
Cassio Posted September 25, 2003 Posted September 25, 2003 (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 September 25, 2003 by Cassio Quote Stream of Consciousness (My blog)
Illusion Posted September 26, 2003 Author Posted September 26, 2003 ok, that groups everything in month 1 .. how do you total it though ... I have like 20 results now I want to sum all results to bring me back one sum? Quote
Cassio Posted September 26, 2003 Posted September 26, 2003 Wich field do you want to sum? Quote Stream of Consciousness (My blog)
*Experts* Nerseus Posted September 26, 2003 *Experts* Posted September 26, 2003 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 Quote "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
zelk Posted September 30, 2003 Posted September 30, 2003 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 Quote
Illusion Posted October 1, 2003 Author Posted October 1, 2003 this may sound stupid, but what does Create Procedure to .. (I'm guessing it creates a procedure, but to what end) Quote
zelk Posted October 1, 2003 Posted October 1, 2003 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 Quote
Illusion Posted October 3, 2003 Author Posted October 3, 2003 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 Quote
wyrd Posted October 3, 2003 Posted October 3, 2003 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. Quote Gamer extraordinaire. Programmer wannabe.
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.