How do I calculate sum of multiple columns of a selected row using query?

Question

0.00/5 (No votes)

See more:

I have an Employee table Access Database with columns 'Employee_ID','Basic Salary', 'HRA', 'TA&DA' and I want to sum these three columns "Basic Salary', 'HRA', 'TA&DA" and get the Total on a textbox.
The query I wrote only appends the values of these three Columns but doesn't sum it.
Please, is there something I didn't do right?
I'm a beginner in programming please.
Thanks.

What I have tried:

VB

Private Sub CalculateGross()

        Dim gross As Double
        'Dim net As Double
        'Dim tax As Double

        If String.IsNullOrEmpty(txtSalaryID.Text) Then MsgBox("Sorry, cannot perform Query. String is Null.") : txtSalaryID.Focus() : Exit Sub

        EmpMain.AddParam("@SalID", txtSalaryID.Text)

        EmpMain.ExecQuery("SELECT SUM([Basic Salary]+ HRA + [TA&DA]) AS Total FROM Employee WHERE Employee_ID = @SalID ")

        If NoErrors(True) = False OrElse EmpMain.RecordCount < 1 Then txtSalaryID.Clear() : txtSalaryID.Focus() : Exit Sub

        EmpMain.DBcon.Open()
        EmpMain.Reader = EmpMain.DBcmd.ExecuteReader

        If EmpMain.Reader.Read() Then
            If EmpMain.Reader("Total") Then
                gross = EmpMain.Reader("Total")
            End If
        End If
        txtGrossPay.Text = gross

        EmpMain.DBcon.Close()

    End Sub

Posted 26-Sep-19 8:05am

Member 14587396

Updated 26-Sep-19 10:08am

Patrice T

v2

Add a Solution

Comments

ZurdoDev 26-Sep-19 14:53pm

SUM(Field1) + SUM(Field2) ... no?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Afzaal Ahmad Zeeshan · Accepted Answer · 2019-09-26T10:08:00

I believe you need to use this query,

SQL

-- Assuming
-- | EmployeeId | Name | JoinDate | ... | [Basic Salary] | HRA | [TA&DA] | ...
SELECT ([Basic Salary] + HRA + [TA&DA]) AS 'Total' 
FROM Employee 
WHERE Employee_ID = @SalID;

Application of an aggregate function (SUM() in this case) makes little sense, why would you spread the Basic Salary of an employee all over the table, and then group it? If you wanted to get a grouping of the values, then maybe SUM() part makes sense, but WHERE query is inappropriate for a group as you might want to apply something like, WHERE Basic Salary > 1000 etc.

Secondly, please name the tables in a better way—TA_DA would make more sense. Similar for Basic_Salary, avoid spacing, and special characters/names. You might know what you did, but the other DBA(s) would always be up for a challenge to break things. :-)

Check out this thread[^] to learn about other ways in which you can get the sum of columns for a record.