Member Avatar for jazzyb

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

Edited by Nick Evan because: Added CODE tags
  • 3 Contributors
  • 2 Replies
  • 116 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by crishjeny

Dani AI

Generated

The original result happened because month was included in the grouping: grouping by month keeps each month as a separate row instead of collapsing rows that share the same pay/allowance. The requirement is to treat each distinct (empid, basicpay, allowance1) combination as one row and count how many months that combination appears in for the target year/bill type.

A straightforward, robust pattern is:

  • apply filters for the year, billtype='new' and allowance1>0 first;
  • either GROUP BY the pay columns and use COUNT(DISTINCT month) (to avoid duplicate-month rows) or compute a partitioned count with a window function to keep row-level details and add the month counter.

Example using a CTE + window function (keeps one row per distinct pay/allowance and returns the month count for that group):

WITH Filtered AS (
  SELECT p.empid, e.firstname, p.basicpay, p.allowance1, p.[month]
  FROM payroll p
  JOIN employees e ON e.empid = p.empid
  WHERE p.[year] = 2011
    AND p.billtype = 'new'
    AND p.allowance1 > 0
)
SELECT DISTINCT empid, firstname, basicpay, allowance1,
       COUNT(*) OVER (PARTITION BY empid, basicpay, allowance1) AS monthcounter
FROM Filtered
ORDER BY empid, basicpay DESC;

Notes and cautions:

  • If the same month can appear more than once for a given empid/pay (duplicate rows), swap the COUNT(*) approach for COUNT(DISTINCT month) in a GROUP BY query to avoid overcounting.
  • Storing months as names is brittle; prefer a numeric month or a date column so DISTINCT and ordering behave predictably.
  • Grouping on decimal pay values can misgroup because of precision — consider rounding/casting if necessary.
  • was correct to group on the pay columns to collapse months; ’s DISTINCT alone won’t produce an aggregate month count without grouping or a windowed aggregate.

Recommended Answers

All 2 Replies

Member Avatar for BitBlt

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
Member Avatar for crishjeny

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 developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.