SQL Query: Please suggest me the output of below Query

Question

1.00/5 (2 votes)

See more:

SQL-Server-2008R2

Employee Table:
ID Name Date Active Salary
1 A 1/04 1 2000
2 B 1/04 0 2000
3 C 3/05 1 4000
4 A 5/05 1 4000
5 B 16/05 1 9000
6 A 2/05 1 3000
7 D 3/08 0 12000

Desired Output:
Name Count Salary
A 3 9000
B 1 11000
C 1 4000
D 0 12000

Posted 11-Jun-15 1:12am

User 8865852

Updated 11-Jun-15 2:47am

v2

Add a Solution

3 solutions

Solution 3

Finally I got the query from the below output:

select A.Name, A.sal, B.present from
(select Name, sum(salary) as sal from EmpWork group by name) A,
(select Name, Sum(status) as present from EmpWork group by name) B
where A.name= b.name

Posted 11-Jun-15 2:45am

User 8865852

Comments

CHill60 11-Jun-15 9:20am

Well you might have got to the result you wanted but this is absolutely the wrong way to go about it.
- First of all - what is "status" - I presume this is "Active" on your original question, which does not appear in your required results. Even if you do need something to do with status there is no concept of "the sum of statuses" - Instead of showing the expected results as "Count" you should have indicated that you wanted the number of Active records.
- I presume "EmpWork" is your employee table from the original question?? If not then this answer is completely off-topic
- Now to the query itself - you are doing two sub-queries and a join when everything can be done in a single pass of the table - see solution 2 but use SUM(Active) instead of COUNT(E.Name)

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)

Torakami · Accepted Answer · 2015-06-11T01:37:00

Solution 1

Your last desired output is not clear ,

but as per my understanding below is the query you are trying.

SQL

Select emp.Name , Count(emp.salary) as Count , Sum(emp.Salary) as Count_Salary
From Employee as emp with (nolock)
Group by emp.Name
Order by emp.Name

Just let me know if you have any further queries.

Posted 11-Jun-15 1:37am

Torakami

Updated 11-Jun-15 2:56am

v3

Comments

_Asif_ 11-Jun-15 8:05am

You missed the count field

Torakami 11-Jun-15 8:55am

Yeh , thanks , i have just updated for his understanding ...

Thanks anyways i will update it

CHill60 11-Jun-15 9:22am

Gives results of D 1 12000 not D 0 12000 as required. See Solution 3 (from OP) and my comment to it.

_Asif_ · Accepted Answer · 2015-06-11T02:05:00

Solution 2

A slight addition in Torakami solution

SQL

Select E.Name , COUNT(NULLIF(E.ACTIVE,0)) as COUNT, Sum(E.Salary) as Salary
From Employee E
Group by E.Name
Order by E.Name

Posted 11-Jun-15 2:05am

_Asif_

Updated 11-Jun-15 18:43pm

v2

Comments

CHill60 11-Jun-15 9:22am

Gives results of D 1 12000 not D 0 12000 as required. See Solution 3 (from OP) and my comment to it.

_Asif_ 12-Jun-15 0:43am

Correct, solution modified!