Sum Distinct Data

[1hrllabs]

I'm running a query on the following type of data

EMPLOYEE PROJECT HOURS
G. Smith BB21 15
G. Jones BB21 20
G. Whiz BB60 40
G. Gee BD00 10

I want to sum the hours of the distinct projects selected in the query. For example, if I select all projects that begin with BB, I would expect the total to equal 75 hours.

Is there a way to do a compound statement keying on both the distinct and the hours?

 

[lespaul]

if I select all projects that begin with BB, I would expect the total to equal 75

So do you want to see:

Project Hours
BB 75

or do you still want it grouped by project but the total hours to be reported:

Project Hours
BB21 75
BB60 75



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[1hrllabs]

Leslie,
I'd like to see the first example you wrote, the one project total: BB = 75 hours.
Joe

 

[lespaul]

select left(Project, 2), sum(hours) From tablename where left(Project, 2) = "BB" GROUP BY left(Project, 2)



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[1hrllabs]

I already have a parameter query with a prompt that goes like this: Like [Project begins with:] & "*" (Parameter query is in the Project field).
So, how would I incorporate the prompted project into your query.
By the way, I'm doing the query via MS Front Page.

 

[lespaul]

well the LIKE operator works a little differently then the LEFT function. The LEFT function returns the number of characters specified (in this case 2). The like operator works like this:

AC*
will find all instances where the string BEGINS with AC
Access
Account

*AC
will find all instances where the string ENDS with AC
Lilac
Prozac

*AC*
will find all instances where the string CONTAINS AC
Access
Account
back
pack
lilac
Prozac

with your example, if the user enters nothing they will get all records returned.

So, it depends on what you want. Are all your projects named with two characters at the beginning? Do you have project DBB?

If you always specify a two character project, then you could do:

select left(Project, 2), sum(hours) From tablename where left(Project, 2) = [Project begins with:] GROUP BY left(Project, 2)

but then the user would always have to enter the two characters in order to get results.

if you do:

select left(Project, 2), sum(hours) From tablename where left(Project, 2) LIKE [Project begins with:] & "*" GROUP BY left(Project, 2)

then if the user enters a single "B" they would get all projects that begin with a "B":

BB
BD






Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[1hrllabs]

Leslie,
Some projects have a four character identifier, e.g., BB01.
Others have a seven character identifier e.g. SD07.123.
These are top level identifiers. (A given project would have trailing task numbers as well)
So, if a project manager wanted to know who charged to project BB21. They would enter BB21 into the prompt dialog box and a list would come up showing who worked on the project.
This part I have coded and working.
What I'm trying to do is have a total for the project queried.
I'm doing this via Front Page and I'm stuck with totalling up the dynamic query.

 

[lespaul]

So you already have the details for the project, you just want a total?

What is the SQL of the existing query? In order to get a "total" in the same result set you need to add a UNION clause to the first query.

Leslie

 

[1hrllabs]

fp_sQry="SELECT * FROM WPLR WHERE (Project LIKE ':roject::%') ORDER BY Hours ASC"
The Total doesn't necessarily have to be in the same result. It can be a stand alone query but based on the first project identifier query.
Joe

 

[PHV]

fp_sQry2 = "SELECT SUM(Hours) AS Total FROM WPLR WHERE Project LIKE ':roject::%'"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[1hrllabs]

PH,
Unfortunately, still doesn't work. Inserted code and came up with database wizard error.
Database Results Wizard Error
The operation failed. If this continues, please contact your server administrator.