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
Yes.
Union All the two table and run a sum aggregate.
Good Luck!!!
~~ CK
6 2168
On your outer GROUP BY, you missed specminutes...
Good Luck!!!
~~ CK
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
yes, posting some sample data and how your output will look like is always better :)
~~ CK
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
Yes.
Union All the two table and run a sum aggregate.
Good Luck!!!
~~ CK
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]
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Narine |
last post by:
Hi All,
We're running SQL Server 2000, SP3.
I have a stored procedure that consists of a single Select statement.
It selects a bunch of columns one of which is a column of data type
TEXT.
SP...
|
by: bobsawyer |
last post by:
I've been building a series of SELECT lists that are populated
dynamically using HTTPRequest. Things are going pretty well, and I've
got the whole thing working flawlessly in Mozilla/Firebird....
|
by: William Wisnieski |
last post by:
Hello Everyone,
I have a main form with a datasheet subform that I use to query by form.
After the user selects two criteria on the main form and clicks the
cmdShowResults button on the main...
|
by: syounger |
last post by:
Hi. I have a report in Access 2000 that is based on selection made
from a series of interdependent list boxes. The boxes I have right now
are Source, Table, Column, Date. The user chooses Source...
|
by: SKG |
last post by:
Hi All
I am using standard code to walk through a multi select list boxes and
build a sql string to filter data, I would also like to store the 2nd
column value for each selected item as well in...
|
by: Zlatko Matić |
last post by:
Hello.
How to reference selected values from a multi-select list box, as a
criteria in a query ?
Is it possible at all?
Regards,
Zlatko
|
by: Ian Richardson |
last post by:
Hi,
The function I've put together below is a rough idea to extend a SELECT
list, starting from:
<body>
<form name="bambam">
<select id="fred">
<option value="1">1</option>
<option...
|
by: Fran |
last post by:
I recently tried to use code for "Use a multi-select list box to
filter a report" from Allen Browne in my database. I was able to add
the code and adapt it to my needs, however I am getting an...
|
by: nomad |
last post by:
Hello everyone:
I new to PHP and I'm reading a book on PHP Bibles 2nd edition. It has to deal with a user-rating system. There are 4 scripts to it.
I'm getting an error
Unknown column...
|
by: hannoudw |
last post by:
Hi I have a table that contains all the informations about the user . i want to when to click edit to edit the information of a user .
I wrote this page : edit_user.php
<?php...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
| |