CODEWITHSUNDEEP
sqlsql-servergroup-bysql-server-2014sql-server-2014-express
jackhelsi
jackhelsi

Reputation: 169

How use select * with group by subquery in sql server

I have table of employees salary details records with columns

Id  Name        Year    Month   Salary
1   ABC         2021    Jan     50000
2   PQR         2021    Jan     40000
3   KLM         2021    Feb     45000
4   LMN         2021    Jan     55000
5   LMN         2022    Jan     20000
6   ABC         2022    Feb     25000
7   ABC         2022    Jan     2500
8   ABC         2022    Dec     60000
9   LMN         2022    Nov     70000

Now I want to find which employee gets salary greater than 100000 from joining, and display employees all data

--find which employee gets more than 100000 salary till now

select  name,sum(salary) as AnnualSalary from tblEmpsalary
group by Name
having sum(Salary)>100000 --this query works

--but below query display no data , (I want to show all data of employee which gets more than 100000 total salary)

SELECT id, name,Month,Year, SUM(Salary) AS TotalSales
FROM tblEmpsalary
GROUP BY name,Id,Month,Year,Salary
having SUM(Salary)>100000;

Upvotes: 0

Views: 101

Answers (3)

Vikram Jain
Vikram Jain

Reputation: 5588

Please, try with below query where one query for grouping and another is joining for fetch employees details:

SELECT TS.id, TS.name, TS.Month,Year, TS.Salary, ATS.TotalSales FROM 
 (SELECT Month, Year, SUM(Salary) AS TotalSales 
 FROM tblEmpsalary 
 GROUP BY Month,Year,Salary
 HAVING SUM(Salary)>100000
 ) AS ATS
 LEFT OUTER JOIN tblEmpsalary TS on ATS.Month = TS.Month and ATS.Year = TS.Year
 ORDER BY TS.name, TS.Id, TS.Month, TS.Year, ATS.TotalSales

Upvotes: 0

Charlieface
Charlieface

Reputation: 71119

You can use a window function for this

SELECT
  id,
  name,
  Month,
  Year,
  TotalSales
FROM (
    SELECT *,
        SUM(Salary) OVER (PARTITION BY name) AS TotalSales
    FROM tblEmpsalary e
) e
WHERE e.TotalSales > 100000;

Upvotes: 0

Sergey
Sergey

Reputation: 5217

 SELECT T.ID,T.Name,T.Year,T.Month,T.Salary
 FROM tblEmpsalary AS T
 JOIN
 (
   select  ID
   from tblEmpsalary
   group by ID
   having sum(Salary)>100000
 )AS X ON T.ID=X.ID

Upvotes: 0

Related Questions