Column 't.specminutes' is invalid in the select list because.......

[dougancil]

I have the following 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

and when I try to parse it, I receive 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.

can someone tell me what I'm missing here? Thank you
Doug

[ck9663]

On your outer GROUP BY, you missed specminutes...

Good Luck!!!

~~ CK

[dougancil]

CK,

Thank you.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.

When I run this query, it's only showing partial information and not all. What do you think I may be doing wrong? I can provide you with sample data, if necessary.

Thank you

Doug

[ck9663]

yes, posting some sample data and how your output will look like is always better :)

~~ CK

[dougancil]

Ck,

Here is some sample data:

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
Christy Clayton 8455 10/1/2010 118.36
Christy Clayton 8455 10/2/2010 192.46
Christy Clayton 8455 10/4/2010 102.04
Christy Clayton 8455 10/7/2010 422.41

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

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

[ck9663]

Yes.

Union All the two table and run a sum aggregate.

Good Luck!!!

~~ CK

[dougancil]

CK,

here's the solution I'm going with.

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