[RESOLVED] Group sum from Db

Afternoon,

I'm sure there must be a way to do this but my database skills through C# aren't particular great so hoping someone can help with where I need to start looking.

I have a database which is used to record sales orders from multiple agents. Several Products can be of the same type. What I need to do is find a way of selecting just the products sold by an agent (which I can determine), grouped by Product and for a given sales date and getting the group value for each product.

I have attached a pic of the basic layout of my data to hopefully explain it a bit better.

Basically I just need to know, say, what Jack sold for a given date as a total for each product so I would end up with a total for Ink, Labels, etc.

Once I've retrieved that information I would then like to store it somewhere, perhaps in TextBoxes.

This solution is to replace my current method of using GridViews to store the information and filter and group it out and then populate TextBoxes with a grouping event as I believe it will give less overhead to my project and simplify things.

Many thanks in advanced!

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

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?

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

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! :)