SQL query not returning all results

Question

SQL query not returning all results

doug 40899

SSCommitted

Points: 1656
More actions
November 16, 2010 at 11:06 am

#74620

I have the following query:
select name, employeenumber, summinutes, sum(summinutes/60) as hours
from (
select scratchpad2.name, scratchpad2.employeenumber, SUM(scratchpad2.minutes) + SUM(scratchpad4.totalminutes) as summinutes
from scratchpad2
inner join scratchpad4
on scratchpad2.employeenumber = scratchpad4.employeenumber
group by scratchpad2.name, scratchpad2.employeenumber
) t
group by name, employeenumber, summinutes
and looking at both scratchpad2 and scratchpad4, I'm seeing more data than is being returned to me. Sample data from scratchpad2:
Eric Edwards 8247 10/1/2010 222.67
Eric Edwards 8247 10/2/2010 428.74
Eric Edwards 8247 10/4/2010 108.41
Eric Edwards 8247 10/5/2010 317.33
Gerald Stephen 8389 10/1/2010 505.92
Gerald Stephen 8389 10/2/2010 458.01
Timothy Bedard 8433 10/1/2010 372.87
Timothy Bedard 8433 10/2/2010 338.46
Timothy Bedard 8433 10/3/2010 139.81
Timothy Bedard 8433 10/4/2010 430.32
and when I run the above query, here is the data that's being returned to me:
Samantha Balash 8442 1508.93 25.148833
Christy Clayton 8455 3045.81 50.763500
Akieva Saunders 8466 1751.11 29.185166
Brenda Brown 8467 2797.97 46.632833
but I'm not sure why this query isn't pulling all of the data. Can anyone tell me why?
Thank you
Doug

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

getoffmyfoot Hall of Fame Points: 3338 More actions · Answer 1

doug 40899 (11/16/2010)
I have the following query:
select name, employeenumber, summinutes, sum(summinutes/60) as hours
from (
select scratchpad2.name, scratchpad2.employeenumber, SUM(scratchpad2.minutes) + SUM(scratchpad4.totalminutes) as summinutes
from scratchpad2
inner join scratchpad4
on scratchpad2.employeenumber = scratchpad4.employeenumber
group by scratchpad2.name, scratchpad2.employeenumber
) t
group by name, employeenumber, summinutes
and looking at both scratchpad2 and scratchpad4, I'm seeing more data than is being returned to me. Sample data from scratchpad2:
Eric Edwards 8247 10/1/2010 222.67
Eric Edwards 8247 10/2/2010 428.74
Eric Edwards 8247 10/4/2010 108.41
Eric Edwards 8247 10/5/2010 317.33
Gerald Stephen 8389 10/1/2010 505.92
Gerald Stephen 8389 10/2/2010 458.01
Timothy Bedard 8433 10/1/2010 372.87
Timothy Bedard 8433 10/2/2010 338.46
Timothy Bedard 8433 10/3/2010 139.81
Timothy Bedard 8433 10/4/2010 430.32
and when I run the above query, here is the data that's being returned to me:
Samantha Balash 8442 1508.93 25.148833
Christy Clayton 8455 3045.81 50.763500
Akieva Saunders 8466 1751.11 29.185166
Brenda Brown 8467 2797.97 46.632833
but I'm not sure why this query isn't pulling all of the data. Can anyone tell me why?
Thank you
Doug

Can you post table definitions please? But even before I get those I'll step out on a shaky limb and fire off a guess that your inner join may be whittling out some results. If the employeenumber doesnt exist in both tables it wont be in your result set.

Lowell SSC Guru Points: 323518 More actions · Answer 2

there's an inner join in your query, which would limit the data to only the employee's that exist in BOTH tables...is that what the problem is?

...

inner join scratchpad4

on scratchpad2.employeenumber = scratchpad4.employeenumber

...

--should be ??

...

left outer join scratchpad4

on scratchpad2.employeenumber = scratchpad4.employeenumber

...

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

doug 40899 SSCommitted Points: 1656 More actions · Answer 3

Lowell,

I changed the inner join to left outer join and it does show all of the employees now but not all of the data (as far as summinutes and hours)

Here's what I get back when I run the now modified query:

Eric Edwards8247NULLNULL

Xavier Oaks8378NULLNULL

Gerald Stephen8389NULLNULL

Michelle Mayes8428NULLNULL

Timothy Bedard8433NULLNULL

Samantha Balash84421508.9325.148833

Wendy Castellanos8454NULLNULL

Christy Clayton84553045.8150.763500

Jordan Campusano8462NULLNULL

Chanel Jones8464NULLNULL

Leticia Guerrero8464NULLNULL

Alisha Byrd8465NULLNULL

Akieva Saunders8466 1751.1129.185166

Brenda Brown84672797.9746.632833

doug 40899 SSCommitted Points: 1656 More actions · Answer 4

Ok ,

I think I see what the problem is here. In scratchpad2, I'm calculating the number of minutes that each employee is logged in and in scratchpad4, that's just for special circumstances, ie holidays, sick time, etc. What I was attempting to do is to sum up for both tables and include the logged in time from scratchpad2.

What I need to have happen is as follows

All of the values in scratchpad2 need to be summed up, AND each employee that is in scratchpad4, needs to have that time added to whatever time that is already in scratchpad2.

Does that make sense?

Lowell SSC Guru Points: 323518 More actions · Answer 5

so, do the calulation from stratchpad2 every time, but items in sp4 are only sometimes there...

like this?: (new calculation for "specminutes")

SELECT

name,

employeenumber,

summinutes,

sum(summinutes/60) as hours,

specminutes,

sum(specminutes/60) as sphours

FROM

(

SELECT

scratchpad2.name,

scratchpad2.employeenumber,

SUM(scratchpad2.minutes) + SUM(scratchpad2.totalminutes) as summinutes,

SUM(scratchpad2.minutes) + SUM(scratchpad4.totalminutes) as specminutes,

FROM scratchpad2

INNER JOIN scratchpad4

ON scratchpad2.employeenumber = scratchpad4.employeenumber

GROUP BY

scratchpad2.name, scratchpad2.employeenumber

) t

GROUP BY

name,

employeenumber,

summinutes

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

doug 40899 SSCommitted Points: 1656 More actions · Answer 6

Lowell,

I ran this query:

SELECT

name,

employeenumber,

summinutes,

sum(summinutes/60) as hours,

specminutes,

sum(specminutes/60) as sphours

FROM

(

SELECT

scratchpad2.name,

scratchpad2.employeenumber,

SUM(scratchpad2.minutes) + SUM(scratchpad2.minutes) as summinutes,

SUM(scratchpad2.minutes) + SUM(scratchpad4.totalminutes) as specminutes

FROM scratchpad2

INNER JOIN scratchpad4

ON scratchpad2.employeenumber = scratchpad4.employeenumber

GROUP BY

scratchpad2.name, scratchpad2.employeenumber

) t

GROUP BY

name,

employeenumber,

summinutes

and get this error:

Column 't.specminutes' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

doug 40899 SSCommitted Points: 1656 More actions · Answer 7

Lowell,

After adding specminutes to the outer group by I resolved that issue BUT it's still showing only 4 entries for results for that period when there should be at least 8 or 9. Any idea why?

Samantha Balash

Christy Clayton

Akieva Saunders

Brenda Brown