-
Feb 7th, 2012, 11:51 AM
#1
Thread Starter
Lively Member
-
Feb 7th, 2012, 12:25 PM
#2
Re: Group sum from Db
well, it's fairly simple... it just depends on when/how you want to filter it out...
the basic query would look like this:
Code:
SELECT Agent, Product, [Date], sum(Value)
FROM Orders
GROUP BY Agent, Product, [Date]
That's the basic query... That will give you what every agent has sold, by product for every day.
from there it's just a matter of filtering out what you want... if you want a specific agent:
Code:
SELECT Agent, Product, [Date], sum(Value)
FROM Orders
WHERE Agent = 'Jeff'
GROUP BY Agent, Product, [Date]
or for a specific date:
Code:
SELECT Agent, Product, [Date], sum(Value)
FROM Orders
WHERE [Date] = #06/02/2012#
GROUP BY Agent, Product, [Date]
or both:
Code:
SELECT Agent, Product, [Date], sum(Value)
FROM Orders
WHERE [Date] = #06/02/2012# and Agent = 'Jeff'
GROUP BY Agent, Product, [Date]
I suggest using parameters though... for help on that, I refer you to out Database FAQ & Tutorial section.
One thing, you may noticed that I used [Date] in the SQL... that's because generally Date is a key/reserved word in many database systems. The [] around it forces it to be recognized as an object name (your field in this case) ... it's generally recommended that such words be avoided in object names by themselves (OrderDate would be an accepted alternative). Same goes with Spaces in table/field names...
-tg
-
Feb 7th, 2012, 04:35 PM
#3
Thread Starter
Lively Member
Re: Group sum from Db
Hi techgnome,
Thanks for taking the time to help.
Ok, I followed (hopefully) the links you provided and some other articles I found and made a quick test app so I could better understand each part and have got the below (no doubt very rough) code.
Code:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Data.OleDb;
namespace WindowsFormsApplication1
{
public partial class Form1 : Form
{
string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="db location";Persist Security Info=True;Jet OLEDB:Database Password=pw";
string queryString = "SELECT Agent, [Date], SUM(RevenueValue) FROM Orders WHERE Agent='Frank' and [Date]=#06/02/2012# GROUP BY Agent, [Date]";
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
OleDbConnection mConnection = new OleDbConnection(connectionString);
OleDbCommand mCommand = new OleDbCommand(queryString, mConnection);
try
{
mConnection.Open();
OleDbDataReader reader = mCommand.ExecuteReader();
while (reader.Read())
{
MessageBox.Show(reader.GetValue(2).ToString());
}
reader.Close();
mCommand.Dispose();
mConnection.Close();
}
catch (Exception ex)
{
MessageBox.Show("Connection failed");
MessageBox.Show(ex.Message);
}
}
}
}
Whilst I can pull out the grouped values when I exclude the [Date], when I try to include it as above it doesn't seem to do anything.
Also, just to make sure, because I'm using Access 2003 it is OleDb I need to use, yes? Plus, I can add in whatever relevant variables when building the queryString?
Last edited by Madcat1981; Jan 6th, 2016 at 10:07 AM.
-
Feb 7th, 2012, 04:46 PM
#4
Re: Group sum from Db
Yes... in your case your query should look like this:
Code:
string queryString = "SELECT Agent, [Date], SUM(RevenueValue) FROM Orders WHERE Agent=? and [Date]=? GROUP BY Agent, [Date]";
the ? denote place holders for the parameters...
Then before executing it... you add your parameters... the trick is to add them in the same order as they appear in the SQL:
Code:
mCommand.Paramers.AddWithValue("Agent", { ... put what ever variable holds the name of the agent here ...});
mCommand.Paramers.AddWithValue("RptDate", { ... put what ever variable holds the date here ...});
The Key to making this work though is that your date variable MUST BE A DATE... do NOT try to pass it as a string....
Code:
string AgentName = "Frank";
date ReportDate = new date(2012, 02, 06); // Feb 6
mCommand.Paramers.AddWithValue("Agent", AgentName);
mCommand.Paramers.AddWithValue("RptDate", ReportDate); // YES!
Code:
string AgentName = "Frank";
string ReportDate = "2/6/2012"; // Feb 6
mCommand.Paramers.AddWithValue("Agent", AgentName);
mCommand.Paramers.AddWithValue("RptDate", ReportDate); // NO! BOO! HISS! NEVAH!
One thing to also remember... in databases, dates do not have a format... so while you see d/m/y ... the realities is that that is jsut a display... not how it's actually stored... that's why it's important to treat dates like dates.. and it might be why your query with hardcoded dates didn't work... instead of pulling Feb 6, it was pulling June 2nd.... using parameters and date variables should overcome that...
-tg
-
Feb 7th, 2012, 05:29 PM
#5
Thread Starter
Lively Member
Re: Group sum from Db
Yep, that's exactly what was causing the problem! Works perfectly now and I also having a better understanding of the process so thank you!
One thing I would like to know though is if it can't find a value for the criteria does it just return nothing? How would I return say a value of £0 where there is no data?
Edit: Not to worry, figured that out.
Again, thanks a lot for your help!
Last edited by Madcat1981; Jan 6th, 2016 at 10:07 AM.
Reason: update
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|