[RESOLVED] SUM() Sql query

[chdboy]

Hi,

CODE:

 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim con = New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\Danial\documents\visual studio 2010\Projects\ESI_PF_Payroll_V1\ESI_PF_Payroll_V1\Pay.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True")
        Dim adaptor As New SqlDataAdapter
        Dim dataset As New DataSet
        con.Open()
        con.Close()
        If Firstname_txt.Text = "" Then
            MsgBox("Firstname Cannot be blank!")
        Else
            con.Open()
            cmd = New SqlCommand(str, con)
            cmd.Connection = con
            cmd.CommandText = "SELECT Firstname FROM Attendance where Firstname ='" & Firstname_txt.Text.Trim & "'"
            cmd.Connection = con
            adaptor.SelectCommand = cmd
            adaptor.Fill(dataset, "O")
            Dim count = dataset.Tables(0).Rows.Count
            If count > 0 Then
                MessageBox.Show("This Employee Does Exist!")
                cmd.CommandText = "SELECT SUM(Present) FROM Attendance WHERE AttendanceStatus = 1 And (Firstname = Firstname) Group BY Firstname"
                cmd = New SqlCommand(cmd.CommandText, con)
                cmd.Connection = con
                dr = cmd.ExecuteReader
                While dr.Read
                    TextBox1.Text = dr(0)
                    'TextBox4.Text = dr(1)
                End While
                con.Close()
            Else
                MsgBox("This Employee does not Exists!")
                Firstname_txt.Text = ""
            End If
        End If

            con.Close()
    End Sub
End Class

Help needed with this Sql command
CODE:

  cmd.CommandText = "SELECT SUM(Present) FROM Attendance WHERE AttendanceStatus = 1 And (Firstname = Firstname) Group BY Firstname"

I want see the sum(Number of attended days of AttendanceStatus Field where column data is 1(1 denotes present here),I can see the data but it gets incremented by 1 or some times it shows the correct data for some Employee name ,but others they are just 1 number behind or 1 number incremented.I don't know why?

[Shaggy Hiker]

You can drop the GroupBY, as that isn't gaining you anything (and probably not costing you anything). However, the problem probably isn't in the code you showed. The SUM is a pretty simple function. It just adds up that field for all records that meet the criteria. It isn't going to add one at times and subtract one at other times. Therefore, the problem is that you are either getting more records than you expect, or Present holds something other than what you expect. What type of field is Present?

Frankly, I would guess that you want COUNT rather than SUM, but without knowing more about the DB structure, that's still just a guess.

[chdboy]

Present is the Column name and it hold the value 1 who ever is present against the name is 1 and I want to count the number or sum against the name.

[Shaggy Hiker]

Well, it isn't coming up with the number you expect, but I wouldn't be blaming SUM.

What I would do, if you have SQL Server Managment Studio, is to put the query in there and run it, but get rid of the SUM. What you want to see is the records returned. If you have any with a present value other than 1, that would account for your problem, or if you aren't getting as many back as you expect, that would also account for it. Either of those is vastly more likely that the chance that SUM is returning the wrong value.

[techgnome]

sigh....
the SQL is screwed up 6 ways to Sunday...

SELECT SUM(Present) FROM Attendance WHERE AttendanceStatus = 1 And (Firstname = Firstname) Group BY Firstname

if Present is a number and it is just 0 or 1, then the sum is OK... but I would have gone with a Count instead...
But let's look at the where clause....

WHERE AttendanceStatus = 1 ... ok... so we're only selecting where they actually attended... which is why I'd go with the count... again, just me....

and (FirstName = FirstName) .... Uh Houston, we've got a problem...you're comparing a field to itself... and since a field value will always equal iteslf (excepting the laws of time and space have been violated, or you're at the control helm of the TARDIS) it will be true... so you're getting ALL RECORDS.... also... First Name??? What if we have a Shaggy Hiker and a Shaggy Runner? "Shaggy" will get 2... but that's no right... and even if it was, which Shaggy is it for? Shaggy Hiker or Shaggy Runner? This is why I like to build the sql in SQL Management Studio, or even Query Builder in Access is better than shooting from the hip in VB like this...

-tg

[chdboy]

cmd.CommandText = "SELECT SUM(Present) FROM Attendance WHERE AttendanceStatus = 1 And (Firstname = Firstname) Group BY Firstname"

If I change above statement to this

cmd.CommandText = "SELECT SUM(Present) FROM Attendance WHERE AttendanceStatus = 1 And Firstname = " & Firstname_txt & "

It gives me an error while reading the data ,that Given name in the textbox is invalid column name,But the name and the Firstname column does exists .
And you are right about the Shaggy Hiker and a Shaggy Runner thing .

[chdboy]

Ok the problem has been solved

 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim con = New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\Danial\documents\visual studio 2010\Projects\ESI_PF_Payroll_V1\ESI_PF_Payroll_V1\Pay.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True")
        Dim adaptor As New SqlDataAdapter
        Dim dataset As New DataSet
        con.Open()
        con.Close()
        If Firstname_txt.Text = "" Then
            MsgBox("Firstname Cannot be blank!")
        ElseIf Lastname_txt.Text.Trim = "" Then
            MsgBox("Last name Cannot be Blank!")
        Else
            con.Open()
            cmd = New SqlCommand(str, con)
            cmd.Connection = con
            cmd.CommandText = "SELECT * FROM Employee where Firstname ='" & Firstname_txt.Text.Trim & "' and Lastname ='" & Lastname_txt.Text.Trim & "';"
            cmd.Connection = con
            adaptor.SelectCommand = cmd
            adaptor.Fill(dataset, "O")
            Dim count = dataset.Tables(0).Rows.Count
            If count > 0 Then
                MessageBox.Show("This Employee Does Exist!")
                Dim str, str1 As String
                 str1 = "Select Employeecode from Employee where Firstname = '" & Firstname_txt.Text.Trim & "' and Lastname = '" & Lastname_txt.Text.Trim & "';"
                cmd = New SqlCommand(str1, con)
                cmd.Connection = con
                dr = cmd.ExecuteReader
                While dr.Read
                    Empcode.Text = dr(0)
                End While
                con.Close()
                con.Open()
                str = "SELECT SUM(Present) FROM Attendance WHERE AttendanceStatus = 1 And Firstname = '" & Firstname_txt.Text.Trim & "';"
                cmd = New SqlCommand(str, con)
                cmd.Connection = con
                Dim _dr As SqlDataReader
                _dr = cmd.ExecuteReader
                While _dr.Read
                    TextBox1.Text = _dr(0)
                End While
                con.Close()
            Else
                MsgBox("This Employee does not Exists!")
                Firstname_txt.Text = ""
                Lastname_txt.Text = ""
            End If
        End If

            con.Close()
    End Sub

Now the sum is also correct against the First name and the Last name ,and also solved the Shaggy Hiker and a Shaggy Runner thing.
Thank you all for guiding me and helping me to solve this issue.many more to come hehe .