Sql-server – Finding the total sum of salary for person who worked at least two years

sql server

Employee table

So, I am looking for a result with Name, salary and age – Salary should be the sum of all he earned, also it should be calculated only for person who has worked more than a year

For Example employee Sunny worked totally 4 years whereas Arpita worked only one year

I tried group by name and calculated totally earning but not able to find only for person who worked for more than a year, I mean at least two years

select name as [Name of Employee], sum(salary) as [Salary Earned] from Employee group by name;

Best Answer

Try this:

SELECT name AS [Name of Employee], 
    SUM(salary) AS [Salary Earned] 
FROM Employee 
GROUP BY name
HAVING MAX(year) > 1;

This will exclude those records whose maximum "year" having is 1, basically those who have not been employed for more than 1 year.

Related Solutions

Sql-server – Writing a query to find all employee salaries that are higher than their managers

By way of an example that addresses all the items @ypercube brought up in his comments on your question, you could redesign your table structure like:

CREATE TABLE Workers 
(
    WorkerID INT NOT NULL CONSTRAINT PK_Posts PRIMARY KEY IDENTITY(1,1)
    , ManagedByWorkerID INT NULL CONSTRAINT FK_Workers_WorkerID REFERENCES Workers(WorkerID)
    , Salary NUMERIC(10,2) 
);

To add some sample data for demo purposes:

INSERT INTO Workers VALUES (NULL, 1000000);
INSERT INTO Workers VALUES (NULL, 50000);
INSERT INTO Workers VALUES (1, 40000);
INSERT INTO Workers VALUES (1, 1000001);
INSERT INTO Workers VALUES (3, 40002);
INSERT INTO Workers VALUES (2, 29999);
INSERT INTO Workers VALUES (2, 60000);
INSERT INTO Workers VALUES (5, 60000);

And finally, the query to return workers who make more than their boss:

SELECT *
FROM dbo.Workers;

SELECT W.WorkerID
    , W.Salary
    , Bosses.WorkerID AS BossID
    , Bosses.Salary AS BossSalary
FROM dbo.Workers W
    INNER JOIN dbo.Workers Bosses ON W.ManagedByWorkerID = Bosses.WorkerID
        AND Bosses.Salary < W.Salary;

The results:

enter image description here

Sql-server – sql server Group by with calculated values

I think your CASE statements may be flawed as you're always going to have validity_name and validity_surname equal to 0, because the name cannot be 3 different things at once.

When do you want the validity_name to equal 1?

Regardless of that fact, I like the APPLY VALUES method of grouping on a computed column.

Using AdventureWorks as an example, this works like so:

USE AdventureWorks;
GO

SELECT
  FirstName,
  ValidName
FROM Person.Person AS p
CROSS APPLY (VALUES(CASE WHEN FirstName = 'Kim' THEN 1 ELSE 0 END)) AS a(ValidName)
GROUP BY FirstName, ValidName

When applied to your query, it should be something like the following (untested, use the example above to build your query):

select
       p.id,
       p.name,
       p.surname,
       p.adress,
       a.validity_name,
       b.validity_surname,
       sum(pr.value) as value
from   [dbo].[_data_CRM_COSTOMER] as p inner join
       [dbo].[_data_CRM_INVOICE] as r on (r.partner_id=p.id) 
        CROSS APPLY (VALUES(case when(p.name not like ('%!!!%') or p.name not like ('%XXX%') or p.name not like ('%???%')) then 0 else 1 END)) AS a(validity_name)
        CROSS APPLY (VALUES(CASE WHEN p.surname not like ('%!!!%') or p.surname not like ('%*XXX%') or p.surname not like ('%???%') then 0 else 1 END)) as b(validity_surname)
group by id, name, validity_name, surname, validity_surname, adress

Best Answer

Related Solutions

Sql-server – Writing a query to find all employee salaries that are higher than their managers

Sql-server – sql server Group by with calculated values

Related Question