Select statement help

[Jacksparrow87]

Hi people,

I was hoping someone could help me out please.

What I need is to do is basically run a sql statement where it shows the company name, counts all the charge codes and the time spent.

For example I have the following statement so far:

        Dim conn As New OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source=" & Application.StartupPath & "\CNS.mdb")
        Dim adapter As New OleDbDataAdapter

        Dim selCommand As New OleDbCommand
        With selCommand
            .CommandText = "Select [Company], [ChaCode], COUNT(ChaCode) As [NumberofChargeCodes],  COUNT(TSpent) As [NumberofTimeSpent] From Log where Dol BETWEEN ? AND ? And CompID = ? Group By [Company], [ChaCode], [TSpent]"
            .CommandType = CommandType.Text
            .Connection = conn
            'Add Parameters 
            .Parameters.Add(New OleDbParameter("@Date1", OleDbType.Date)).Value = dtp.Value
            .Parameters.Add(New OleDbParameter("@Date2", OleDbType.Date)).Value = dtp2.Value
            .Parameters.Add(New OleDbParameter("@CompID", OleDbType.Integer)).Value = cmbcomp.SelectedValue
        End With
        'assign select command to data adapter 
        adapter.SelectCommand = selCommand
        Dim dt As New DataTable("Log")
        adapter.Fill(dt)
        dglog.DataSource() = dt
        dt.DefaultView.AllowNew = False

What the above coding does is list the answer as follows:

Company name,Charge Code, Count, Time Spent
Adidas, ABS, 5, 5
Adidas, ABS, 7, 7
Adidas, RYS. 4, 4

However I want it as follows:

Company name,Charge Code, Count, Time Spent
Adidas, ABS, 12, 12
Adidas, RYS. 4, 4

Any help please?

 

[Jacksparrow87]

Tried the following:

.CommandText = "Select [Company], [ChaCode], COUNT(ChaCode) As [NumberofChargeCodes From Log where Dol BETWEEN ? AND ? And CompID = ? Group By [Company], [ChaCode]"

Which shows everything correctly (apart from the time spent column) So I decided to include the Time Spent column which I assume will have to be a SUM function as I want to add up all the time spent, so my statement has been changed to:

.CommandText = "Select [Company], [ChaCode], COUNT(ChaCode) As [NumberofChargeCodes],  SUM(TSpent) As [NumberofTimeSpent] From Log where Dol BETWEEN ? AND ? And CompID = ? Group By [Company], [ChaCode], [TSpent]"

However this totally shows the wrong data as the Charge Code column is no longer counted but listed per charge code and the time spent is not showing the correct answer.

 

[Jacksparrow87]

Problem Solved!