Need help for distinct...

[gunapriyan]

Dear Pals,

I have a table named "Payment". It contais the following stucture.

Emp_Name
Emp_Category
Plucked_Date
Rate_of_Wages
Plucked_Days
Sundry_Days
Plucked_Kilos
Plucking_Wages
Sundry_Wages
Incentive_Kilos
Plucking_Incentive
Extra_Hour
Extra_Wages
Sunday_Days
Sunday_Wages
Total_Wages
Advance_Recovery
Total_Payable

The daily entry will fell down into this table…
When I mention 2 days of “Plucked_Days” , it must come single entry for each employee…But now it comes like,
“Guna”, “Dhanush”,”Muthu”, “Karnan” employees came 2 times….I need single time….But I have used distinct……But no result…
Emp_Name
Emp_Category
Plucked_Date
Rate_of_Wages
Plucked_Days
Sundry_Days
Plucked_Kilos
Plucking_Wages
Sundry_Wages
Incentive_Kilos
Plucking_Incentive
Extra_Hour
Extra_Wages
Sunday_Days
Sunday_Wages
Total_Wages
Advance_Recovery
Total_Payable
Guna
Male

5/22/2010

116

0

1

0

0

116

0

0

0

0

0

0

116

0

116

Muthu
Female

5/22/2010

100

1

0

34

100

0

4

2

1

10

0

0

112

0

112

Dhanush
Female

5/22/2010

100

1

0

34

100

0

4

2

0

0

0

0

102

0

102

Karnan
Supervisor

5/22/2010

120

0

1

0

0

120

0

0

0

0

0

0

120

0

120

Guna
Male

5/23/2010

116

0

1

0

0

116

0

0

0

0

0

0

116

0

116

Muthu
Female

5/23/2010

100

0

1

0

0

100

0

0

0

0

0

0

100

0

100

Dhanush
Female

5/23/2010

100

0

1

0

0

100

0

0

0

0

0

0

100

0

100

Karnan
Supervisor

5/23/2010

120

0

1

0

0

120

0

0

0

0

0

0

120

0

120

To that I have used following query,



SELECT a.emp_name AS Emp_Name, Sum(a.plucked_days) AS SumOfplucked_days, a.rate_of_wages AS rate_of_wages,
Sum(a.sundry_days) AS SumOfsundry_days, Sum(a.plucked_Kilos) AS SumOfplucked_Kilos, a.plucking_wages AS plucking_wages,
a.sundry_wages AS sundry_wages, Sum(a.incentive_kilos) AS SumOfincentive_kilos, Sum(a.plucking_incentive) AS SumOfplucking_incentive, Sum(a.extra_hour) AS SumOfextra_hour, Sum(a.extra_wages) AS SumOfextra_wages, a.sunday_wages AS sunday_wages,
Sum(a.total_wages) AS SumOftotal_wages, Sum(a.advance_recovery) AS SumOfadvance_recovery, Sum(a.total_payable) AS SumOftotal_payable
FROM (SELECT b.emp_name FROM (SELECT DISTINCT emp_name FROM payment GROUP BY emp_name) AS b GROUP BY b.emp_name)
AS c INNER JOIN payment AS a ON c.emp_Name=a.emp_Name
WHERE a.plucked_date between cdate('22/05/2010') and cdate('23/05/2010')
GROUP BY a.emp_Name,a.rate_of_wages, a.plucking_wages, a.sundry_wages, a.sunday_wages;

I am waiting for your nice reply.
Thanks in advance..
Yours
GUNA…..

[apr pillai]

When you use DISTINCT Clause all fields used in the Query should have duplicate values to pick only one out of their duplicates. If you look at both Records of 'Dhanush' in the output you have given you can see that the Date Values (5/22/2010 and 5/23/2010) are different in both records.

If you eliminate the plucked_date field from the output column it may work.

[gunapriyan]

Dear Boss,

Thanks for your reply.

I wish to give some more informations to you regarding my need.


Actually my need is that I need a single row for each employee with manipulation.

All the daily entries will falling in Payment Table...

7 days all employees are worked in different manner i.e plucking, sundry and sunday......

One employee may work in plucking and sundry and sunday....

Consider me, I am working monday as plucker, Tuesday Plucking, Wednesday sundry, thursday sundry, friday plucking, saturday sundry, sunday sunday......

So My consolidation is Plucking is 3 days....Sundry is 3 Days.....Sunday is 1 day...

Normally the user will take the report weekly once.....

At the week end my data must display in report as follow,

"Guna" 3 3 1 375 425 110 910

But now my query gets the data as follow

"Guna" 3 0 0 375 0 0 375

"Guna" 0 3 0 0 425 0 425

"Guna" 0 0 1 0 0 110 110


I have used distinct. But no use. To rectify that I need your help. I request you to please change the query and send it to me...

So I request you to please change the query and send it to me back. Please pals, due to very urgent, I need your full cooperation immediately.

Thanks in advance...

Guna