Count rows of each type of distinct row

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Count rows of each type of distinct row

Author

Topic

jazzyb
Starting Member

20 Posts

Posted - 2011-04-20 : 08:27:29

Dear Friends,

I am new to sql,

I have 2 tables named as payroll and employees.

My payroll table has many columns as per my requirement. But I am giving here only few columns of my table.Few columns of payroll table has data in following format:

empid, month, year, basicpay, allowance1, billtype
1, January, 2011, 5000, 1000, new
2, January, 2011, 4000, 900, new
3, January, 2011, 3000, 800, new
1, February, 2011, 5000, 1000, new
2, February, 2011, 4000, 900, new
3, February, 2011, 3000, 800, new
1, March, 2011, 5500, 1200, new
2, March, 2011, 4500, 920, new
3, March, 2011, 3000, 800, new
.
.
.
.
.
I need a select statement which results the output in following format:

empid, firstname, BASICPAY, allowance1, monthcounter(This monthcounter multiplication factor)
1, Sam, 5000, 1000, 2
1, Sam, 5500, 1200, 1
2, Robert, 4000, 900, 2
2, Robert, 4500, 920, 1
3, Smartman, 3000, 800, 3

I used the following query(This is part of my query), It is resulting all rows for the given conditions, but I want distinct rows and monthcounter as the multiplication factor for each distinct row.

SELECT payroll.empid,
SUM(payroll.allowance1) AS allowance1,
payroll.month AS month,
SUM(CASE WHEN payroll.Year=2011 AND billtype='new' AND payroll.allowance1 > 0 THEN payroll.[basicpay] ELSE 0 END) AS BASICPAY,
employees.firstname
FROM payroll INNER JOIN employees ON payroll.empid = employees.empid
WHERE (payroll.year=2011)
GROUP BY month, payroll.empid, employees.firstname
ORDER BY payroll.empid,payroll.month

So I need your kind help,

Thanks

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-04-20 : 08:48:07

SELECT DISTINCT empid,BasicPay,Allowance1,
COUNT(empid) OVER (PARTITION BY empid,BasicPay,Allowance1) AS monthcounter
FROM PAYROLL

http://connectsql.blogspot.com/2011/03/sql-server-magical-over-clause.html

jazzyb
Starting Member

20 Posts

Posted - 2011-04-22 : 10:16:16

Dear Friend,

Thanks for your suggestion, I used Over (Partition by) and it helped me to get the desierd results.

You are great person......

bye

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources