SQl query not pulling all results

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SQl query not pulling all results

Author

Topic

dougancil
Posting Yak Master

217 Posts

Posted - 2010-11-16 : 12:48:11

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-11-16 : 13:06:38

Does the inner query return the right results? Maybe you need a LEFT JOIN instead?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-11-17 : 04:48:46

It'd be hard t otell since the names in your sample data don't match uyp with your displayed results. Can you post "data insert" statements per th eadvised style in the FAQ....so that somebody here can build your tables locally with content. then they could see your code in execution.

dougancil
Posting Yak Master

217 Posts

Posted - 2010-11-17 : 09:28:04

Tara,

I have 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.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

but when I try to run it, I get the following 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.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-11-17 : 11:29:59

Your error is on line 6. You can't add a column in there unless you use an aggregate function or include it in the group by.

In order for us to help further, please follow Andrew's post to help us help you.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

dougancil
Posting Yak Master

217 Posts

Posted - 2010-11-17 : 12:04:33

Tara,

I've looked for what Andrew posted in the FAQ's and can't find that. Can you please post the link.

Thank you.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-11-17 : 12:33:58

Here you go: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

dougancil
Posting Yak Master

217 Posts

Posted - 2010-11-17 : 14:26:18

Tara,

I have sample data, is there any way I can e mail that? I have quite a bit of it and I don't want to post it here.

Thank you

Doug

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-11-17 : 14:28:32

We provide free help on SQLTeam, publicly. Private help is a whole 'nother thing.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

dougancil
Posting Yak Master

217 Posts

Posted - 2010-11-17 : 15:19:14

Here is some sample data then from scratchpad2:

Name Employeenumber Dateonly minutes

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
Eric Edwards 8247 10/6/2010 332.7
Eric Edwards 8247 10/7/2010 59.82
Xavier Oaks 8378 10/1/2010 223.46
Xavier Oaks 8378 10/2/2010 145.2
Xavier Oaks 8378 10/3/2010 380.39
Xavier Oaks 8378 10/4/2010 337.92
Xavier Oaks 8378 10/5/2010 227.53
Gerald Stephen 8389 10/1/2010 505.92
Gerald Stephen 8389 10/2/2010 458.01
Gerald Stephen 8389 10/3/2010 70.32
Gerald Stephen 8389 10/4/2010 124.91
Gerald Stephen 8389 10/7/2010 294.96
Michelle Mayes 8428 10/2/2010 203.35
Michelle Mayes 8428 10/3/2010 469.9
Michelle Mayes 8428 10/5/2010 231.41
Michelle Mayes 8428 10/6/2010 231.72
Michelle Mayes 8428 10/7/2010 322.32
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
Timothy Bedard 8433 10/5/2010 229.47
Timothy Bedard 8433 10/6/2010 386.95

from scratchpad4:

Employeenumber Name Exceptiondate Code Totalminutes

8455 Christy Clayton 10/1/2010 Special Project 60
8455 Christy Clayton 10/2/2010 Approved Technical Reason 60
8466 Akieva Saunders 10/2/2010 Supervisor Meeting 60
8467 Brenda Brown 10/3/2010 Coaching Session 7
8442 Samantha Balash 10/4/2010 Approved Technical Reason 20
8455 Christy Clayton 10/5/2010 Special Project 15
I hope that this helps.

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-11-17 : 15:22:29

Please follow the instructions in the link. It'll take a bit of time on your part, but remember we are offering free help here.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

dougancil
Posting Yak Master

217 Posts

Posted - 2010-11-17 : 16:55:50

Here's the DDL for scratchpad2:

CREATE TABLE [dbo].[scratchpad2] (
[name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[employeenumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[dateonly] [datetime] NULL ,
[minutes] [decimal](10, 2) NULL

and scratchpad4:

CREATE TABLE [dbo].[scratchpad4] (
[Employeenumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Exceptiondate] [datetime] NULL ,
[code] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[totalminutes] [int] NULL

What I'm looking to have happen is that upon execution of the query that it will sum for all in scratchpad2, and also if a name is in scratchpad4, it adds those as well. So in the case of Christy Clayton her total would be 970.27 (if you add all from both scratchpad2 and 4) and give you a total of both, but in the case of someone like Eric, since he doesnt have any values in scratchpad4, his sum would only be for scratchpad2 which would be 1469.67. Does this make sense?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-11-17 : 16:58:53

Here is an example of one of my posts where I asked for help: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=42516

Notice the level of detail and all information provided from that link. Your post will need to do the same.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

dougancil
Posting Yak Master

217 Posts

Posted - 2010-11-18 : 11:13:18

Tara,

I hope that this is more what you were asking for

SELECT
name,
employeenumber,
summinutes,
sum(summinutes/60) as hours,
specminutes,
FROM
(
SELECT
scratchpad2.name,
scratchpad2.employeenumber,
SUM(scratchpad2.minutes) + SUM(scratchpad2.totalminutes) as summinutes,
SUM(scratchpad2.minutes) + SUM(scratchpad4.totalminutes) as specminutes,
FROM scratchpad2
WHERE DATE between '10/1/2010' and '10/8/2010'
INNER JOIN scratchpad4
ON scratchpad2.employeenumber = scratchpad4.employeenumber
GROUP BY
scratchpad2.name, scratchpad2.employeenumber
) t
GROUP BY
name,
employeenumber,
summinutes

CREATE TABLE [dbo].[scratchpad2] (
[name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[employeenumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[dateonly] [datetime] NULL ,
[minutes] [decimal](10, 2) NULL

and scratchpad4:

CREATE TABLE [dbo].[scratchpad4] (
[Employeenumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Exceptiondate] [datetime] NULL ,
[code] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[totalminutes] [int] NULL

from scratchpad2:

Name Employee Number Date Minutes

SELECT Eric Edwards 8247 10/1/2010 222.67
SELECT Eric Edwards 8247 10/2/2010 428.74
SELECT Eric Edwards 8247 10/4/2010 108.41
SELECT Eric Edwards 8247 10/5/2010 317.33
SELECT Eric Edwards 8247 10/6/2010 332.7
SELECT Eric Edwards 8247 10/7/2010 59.82
SELECT Xavier Oaks 8378 10/1/2010 223.46
SELECT Xavier Oaks 8378 10/2/2010 145.2
SELECT Xavier Oaks 8378 10/3/2010 380.39
SELECT Xavier Oaks 8378 10/4/2010 337.92
SELECT Xavier Oaks 8378 10/5/2010 227.53
SELECT Gerald Stephen 8389 10/1/2010 505.92
SELECT Gerald Stephen 8389 10/2/2010 458.01
SELECT Gerald Stephen 8389 10/3/2010 70.32
SELECT Gerald Stephen 8389 10/4/2010 124.91
SELECT Gerald Stephen 8389 10/7/2010 294.96
SELECT Michelle Mayes 8428 10/2/2010 203.35
SELECT Michelle Mayes 8428 10/3/2010 469.9
SELECT Michelle Mayes 8428 10/5/2010 231.41
SELECT Michelle Mayes 8428 10/6/2010 231.72
SELECT Michelle Mayes 8428 10/7/2010 322.32
SELECT Timothy Bedard 8433 10/1/2010 372.87
SELECT Timothy Bedard 8433 10/2/2010 338.46
SELECT Timothy Bedard 8433 10/3/2010 139.81
SELECT Timothy Bedard 8433 10/4/2010 430.32
SELECT Timothy Bedard 8433 10/5/2010 229.47
SELECT Timothy Bedard 8433 10/6/2010 386.95
SELECT Wendy Castellanos 8454 10/1/2010 198.1
SELECT Wendy Castellanos 8454 10/2/2010 176.98
SELECT Wendy Castellanos 8454 10/4/2010 342.52
SELECT Wendy Castellanos 8454 10/5/2010 34.92
SELECT Wendy Castellanos 8454 10/6/2010 280.39
SELECT Christy Clayton 8455 10/1/2010 118.36
SELECT Christy Clayton 8455 10/2/2010 192.46
SELECT Christy Clayton 8455 10/4/2010 102.04
SELECT Christy Clayton 8455 10/7/2010 422.41
SELECT Jordan Campusano 8462 10/1/2010 175.95
SELECT Jordan Campusano 8462 10/2/2010 92.21
SELECT Jordan Campusano 8462 10/3/2010 117.28
SELECT Jordan Campusano 8462 10/5/2010 410.94
SELECT Jordan Campusano 8462 10/6/2010 168.13
SELECT Jordan Campusano 8462 10/7/2010 205.82
SELECT Chanel Jones 8464 10/1/2010 178.97
SELECT Chanel Jones 8464 10/2/2010 179.94
SELECT Chanel Jones 8464 10/3/2010 80.04
SELECT Chanel Jones 8464 10/4/2010 357.81
SELECT Chanel Jones 8464 10/6/2010 179
SELECT Chanel Jones 8464 10/7/2010 179.23
SELECT Leticia Guerrero 8464 10/1/2010 178.97
SELECT Leticia Guerrero 8464 10/2/2010 179.94
SELECT Leticia Guerrero 8464 10/3/2010 80.04
SELECT Leticia Guerrero 8464 10/4/2010 357.81
SELECT Leticia Guerrero 8464 10/6/2010 179
SELECT Leticia Guerrero 8464 10/7/2010 179.23
SELECT Alisha Byrd 8465 10/1/2010 122.38
SELECT Alisha Byrd 8465 10/2/2010 211.38
SELECT Alisha Byrd 8465 10/3/2010 580.95
SELECT Alisha Byrd 8465 10/4/2010 160.36
SELECT Alisha Byrd 8465 10/5/2010 72.79
SELECT Alisha Byrd 8465 10/7/2010 236.18
SELECT Akieva Saunders 8466 10/1/2010 251.88
SELECT Akieva Saunders 8466 10/2/2010 414.79
SELECT Akieva Saunders 8466 10/3/2010 93.63
SELECT Akieva Saunders 8466 10/4/2010 251.64
SELECT Akieva Saunders 8466 10/7/2010 439.17
SELECT Brenda Brown 8467 10/1/2010 355.32
SELECT Brenda Brown 8467 10/2/2010 139.49
SELECT Brenda Brown 8467 10/3/2010 376.58
SELECT Brenda Brown 8467 10/4/2010 598.56
SELECT Brenda Brown 8467 10/5/2010 394.66
SELECT Brenda Brown 8467 10/6/2010 432.6
SELECT Brenda Brown 8467 10/7/2010 451.76

from scratchpad4:

Employeenumber Name ExceptionDate Code TotalMinutes

SELECT 8455 Christy Clayton 10/1/2010 Special Project 60
SELECT 8455 Christy Clayton 10/2/2010 Approved Technical Reason 60
SELECT 8466 Akieva Saunders 10/2/2010 Supervisor Meeting 60
SELECT 8467 Brenda Brown 10/3/2010 Coaching Session 7
SELECT 8442 Samantha Balash 10/4/2010 Approved Technical Reason 20
SELECT 8455 Christy Clayton 10/5/2010 Special Project 15

SUM Scratchpad2.minutes as summinutes, sum Scratchpad2.Minutes + scratchpad4.Totalminutes as Specminutes /60 as sumhours
where date is between 10/1/2010 and 10/8/2010, JOIN INNER JOIN scratchpad4
ON scratchpad2.employeenumber = scratchpad4.employeenumber
GROUP BY
scratchpad2.name, scratchpad2.employeenumber

Result set:

Name EmployeeNumber summinutes sumhours specminutes
Eric Edwards 8247 1469.67 24.4945 NULL
Xavier Oaks 8378 1314.5 21.9083 NULL
Gerald Stephen 8389 1454.12 24.2353 NULL
Michelle Mayes 8428 1458.7 24.3116 NULL
Timothy Bedard 8433 1897.88 31.6313 NULL
Samantha Balash 8442 2817.86 46.964333 20
Wendy Castellanos8454 1032.91 17.2151 NULL
Christy Clayton 8455 5011.62 83.52700 75
Jordan Campusano8462 1170.33 19.5055 NULL
Chanel Jones 8464 1154.99 19.2498 NULL
Alisha Byrd 8465 1384.04 23.0673 NULL
Akieva Saunders 8466 2902.22 48.370333 60
Brenda Brown 8467 5497.94 91.632333 7

What I'm looking to have happen is that upon execution of the query that it will sum for all in scratchpad2,
and also if a name is in scratchpad4, it adds those as well. So in the case of Christy Clayton her total would be 970.27
(if you add all from both scratchpad2 and 4) and give you a total of both, but in the case of someone like Eric,
since he doesnt have any values in scratchpad4, his sum would only be for scratchpad2 which would be 1469.67. Does this make sense?

Thank you,

Doug

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-18 : 11:23:24

the best way to do this is like


SELECT [Employee Number],[Name],[Date],SUM([Minutes]) AS summinutes,SUM([Minutes])/60 AS sumhours,
SUM(CASE WHEN Cat=2 THEN [Minutes] ELSE 0 END) AS specminutes
FROM
(
SELECT [Employee Number],[Name],[Date],[Minutes],1 AS Cat
FROM Scratchpad2
UNION ALL
SELECT [EmployeeNumber],[Name],[ExceptionDate],[TotalMinutes],2
FROM ScratchPad4
)t
GROUP BY [Employee Number],[Name],[Date]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dougancil
Posting Yak Master

217 Posts

Posted - 2010-11-18 : 12:09:48

Visakh,

That sums them for each day individually but I need a sumtotal of all times for each for the week. What I get when I use your query is as follows:

Result Set:

8247 Eric Edwards 2010-10-01 00:00:00.000 222.67 3.711166 .00
8247 Eric Edwards 2010-10-02 00:00:00.000 428.74 7.145666 .00
8247 Eric Edwards 2010-10-04 00:00:00.000 108.41 1.806833 .00
8247 Eric Edwards 2010-10-05 00:00:00.000 317.33 5.288833 .00
8247 Eric Edwards 2010-10-06 00:00:00.000 332.70 5.545000 .00
8247 Eric Edwards 2010-10-07 00:00:00.000 59.82 .997000 .00

What I'd like is
Result Set:

Name EmployeeNumber summinutes sumhours specminutes
8247 Eric Edwards 1469.67 24.4945 NULL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-18 : 12:12:09

ok then a small tweak


SELECT [Employee Number],[Name],SUM([Minutes]) AS summinutes,SUM([Minutes])/60 AS sumhours,
SUM(CASE WHEN Cat=2 THEN [Minutes] ELSE 0 END) AS specminutes
FROM
(
SELECT [Employee Number],[Name],[Date],[Minutes],1 AS Cat
FROM Scratchpad2
UNION ALL
SELECT [EmployeeNumber],[Name],[ExceptionDate],[TotalMinutes],2
FROM ScratchPad4
)t
GROUP BY [Employee Number],[Name]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dougancil
Posting Yak Master

217 Posts

Posted - 2010-11-18 : 14:15:13

visakh,

That does work but one other thing, I need it to match a where clause

where date between '10/1/2010' and '10/8/2010'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-19 : 11:00:40

[code]
SELECT [Employee Number],[Name],SUM([Minutes]) AS summinutes,SUM([Minutes])/60 AS sumhours,
SUM(CASE WHEN Cat=2 THEN [Minutes] ELSE 0 END) AS specminutes
FROM
(
SELECT [Employee Number],[Name],[Date],[Minutes],1 AS Cat
FROM Scratchpad2
where date between '10/1/2010' and '10/8/2010'
UNION ALL
SELECT [EmployeeNumber],[Name],[ExceptionDate],[TotalMinutes],2
FROM ScratchPad4
where ExceptionDate between '10/1/2010' and '10/8/2010'
)t
GROUP BY [Employee Number],[Name]
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dougancil
Posting Yak Master

217 Posts

Posted - 2010-11-23 : 15:21:00

Visakh,

Thank you. That's what I was looking for.

Doug

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-24 : 10:33:58

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources