• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 570
  • Last Modified:

SQL query for multiple records based on one column value

Hi,
Please see attached screen shot of current database table [employeeHours]
I need to write a query, or set of queries that will do the following.

Select a particular office using officeid, in the case of the screen shot it's "Smithtown".

The take each empid and add up the values in the columns f,a,s,v,o for each empid.

So I need to take all rows for empid [employee] 107 and add up f,a,s,v,o
Then all rows for employee 111 and add up f,a,s,v,o
And so on for each empid.

I'm not sure exactly how to do that. I am using a scripting language (perl) to pull these results so I can do some manipulation or multiple queries if need be. it doesn't HAVE To be all in one query but it's fine if it is.

ideas?

Thanks!
Nacht
Screen-shot-2013-12-11-at-8.58.1.png
0
nachtmsk
Asked:
nachtmsk
1 Solution
 
Shaun KlineLead Software EngineerCommented:
Not familiar with MySQL, but in MS SQL, your requirements are a standard GROUP By query:

SELECT empID, sum(f) TotalF, sum(a) TotalA, sum(s) TotalS, sum(v) TotalV, sum(o) TotalO
FROM employeeHours
WHERE office = 'Smithtown'
GROUP BY empID

If you need to pull other columns that vary between rows, such as the date columns, this will not work unless you also use an aggregate function (i.e. max/min) on those columns as well.
0
 
AshokCommented:
SELECT empID, office , sum(f) TotalF, sum(a) TotalA, sum(s) TotalS, sum(v) TotalV, sum(o) TotalO
FROM employeeHours
GROUP BY empID, office
0
 
QuinnDexCommented:
i assume you wanted all the columns added together

SELECT empID, office , f+a+s+v+o) as TotalFASVO
FROM [employeeHours]

Open in new window

0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now