sql doubt

[nikhilboorla]

There are 2 tables

(a) having 50,000 rows with names, date and hours worked

(b) having hourly rates by name of about 100 people.

How to calculate total time spend and cost by person?

 

names	date	hours
RAM	2019-05-05	8
RAJ	2019-05-05	8
RAJESH	2019-05-05	8

 

name	hourly rates
RAM	30$
RAJ	25$
RAJESH	40$

[Biskot2]

inner join 2 tables.

select

  A.names

,A.hours

, B.Hourlyrates

from

Table A INNER JOIN Table B

on  A.names = B.name

[nikhilboorla]

10 minutes ago, Biskot2 said:

inner join 2 tables.

select

  A.names

,A.hours

, B.Hourlyrates

from

Table A INNER JOIN Table B

on  A.names = B.name

Thanks but,i want to calculate total time spent and total cost for each person.

Do i need to do like Sum(A.Hours),Sum(B.Hourlyrates)?

[Biskot2]

37 minutes ago, nikhilboorla said:

Thanks but,i want to calculate total time spent and total cost for each person.

Do i need to do like Sum(A.Hours),Sum(B.Hourlyrates)?

try distinct

select DISTINCT   A.names ,A.hours, B.Hourlyrates from

Table A INNER JOIN Table B

on  A.names = B.name

 

and see

[nikhilboorla]

20 minutes ago, Biskot2 said:

try distinct

select DISTINCT   A.names ,A.hours, B.Hourlyrates from

Table A INNER JOIN Table B

on  A.names = B.name

 

and see

i get the output but emp names are getting repeated.it should be sum of working hours and total cost for each employee.

1	RAMESH	8.0	25.0
2	RAJ	7.0	30.0
3	RAJ	8.0	30.0
4	RAMESH	7.0	25.0
5	RAJESH	8.5	40.0
6	RAJESH	6.5	40.0
7	RAM	8.0	30.0

[Biskot2]

7 minutes ago, nikhilboorla said:

i get the output but emp names are getting repeated.it should be sum of working hours and total cost for each employee.

1	RAMESH	8.0	25.0
2	RAJ	7.0	30.0
3	RAJ	8.0	30.0
4	RAMESH	7.0	25.0
5	RAJESH	8.5	40.0
6	RAJESH	6.5	40.0
7	RAM	8.0	30.0

try this 

 

select DISTINCT   A.names  ,  sum( A.hours), sum(B.Hourlyrates) from

Table A INNER JOIN Table B

on  A.names = B.name  

GROUP BY A.names

[nikhilboorla]

10 minutes ago, Biskot2 said:

try this 

 

select DISTINCT   A.names  ,  sum( A.hours), sum(B.Hourlyrates) from

Table A INNER JOIN Table B

on  A.names = B.name  

GROUP BY A.names

I get below error

"SELECT list expression references A.hours which is neither grouped nor aggregated "

 

[nikhilboorla]

4 minutes ago, nikhilboorla said:

I get below error

"SELECT list expression references A.hours which is neither grouped nor aggregated "

 

I get same error

[Biskot2]

Just now, nikhilboorla said:

I get same error

pm chesina chudu

[former]

This is very basic SQL query?

nevu edo comedy chethunattu vunnavu. 

Else, read some sql basics in w3schools and try it.