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

Recommended Answers

Try this:

SELECT payroll.empid,
employees.firstname,
payroll.basicpay,
payroll.allowance1,
count(month) as monthcounter
FROM payroll 
INNER JOIN employees 
ON payroll.empid = employees.empid
WHERE payroll.year=2011
GROUP BY payroll.empid, employees.firstname, payroll.basicpay, payroll.allowance1
ORDER BY payroll.empid
Jump to Post

All 2 Replies

Try this:

SELECT payroll.empid,
employees.firstname,
payroll.basicpay,
payroll.allowance1,
count(month) as monthcounter
FROM payroll 
INNER JOIN employees 
ON payroll.empid = employees.empid
WHERE payroll.year=2011
GROUP BY payroll.empid, employees.firstname, payroll.basicpay, payroll.allowance1
ORDER BY payroll.empid

use select distinct payroll.empid,
employees.firstname,
payroll.basicpay,
payroll.allowance1,
count(month) AS monthcounter
FROM payroll
for distinct rows

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.20 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.