Results 1 to 5 of 5

Thread: [RESOLVED] Group sum from Db

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Mar 2007
    Posts
    84

    Resolved [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!
    Attached Images Attached Images  
    Last edited by Madcat1981; Jan 6th, 2016 at 10:07 AM.

  2. #2
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Mar 2007
    Posts
    84

    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.

  4. #4
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  5. #5

    Thread Starter
    Lively Member
    Join Date
    Mar 2007
    Posts
    84

    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
  •  



Click Here to Expand Forum to Full Width