?
Solved

SQL Syntax: SUM as part of JOIN statements

Posted on 2007-08-09
9
Medium Priority
?
444 Views
Last Modified: 2013-11-05
Given a working sql statement:

SELECT events.*, venuedb.name AS venue_name, venuedb.city AS venue_city, clients.organization, clients.first_name+' '+clients.last_name AS client_name, client_type
FROM (
(events INNER JOIN venuedb ON events.event_location_id = venuedb.venueid)
INNER JOIN clients ON events.clientid = clients.clientid)
INNER JOIN djemployee ON events.assigned_dj = djemployee.djemployeeid

I want to include the following:

SUM (events.assigned_dj_fee+events.assigned_dj2_fee+events.assigned_dj3_fee+events.assigned_dj4_fee+events.assigned_dj5_fee) AS event_employee_wages

I have tried placing this in a few different places in the query, but so far no joy.

I am using MS Access for the Database and Classic ASP.

If this is possible, will it slow down the query quite a bit? Otherwise I can create a separate recordset, put it into an array and cross-reference

Maybe a left join would be a better idea?
0
Comment
Question by:djlurch
  • 4
  • 3
  • 2
9 Comments
 
LVL 22

Accepted Solution

by:
JimBrandley earned 1000 total points
ID: 19664209
Have you tried it like this:
SELECT events.*, venuedb.name AS venue_name, venuedb.city AS venue_city, clients.organization,
       clients.first_name+' '+clients.last_name AS client_name, client_type,
       SUM (events.assigned_dj_fee, events.assigned_dj2_fee, events.assigned_dj3_fee, events.assigned_dj4_fee, events.assigned_dj5_fee) AS event_employee_wages
FROM (
(events INNER JOIN venuedb ON events.event_location_id = venuedb.venueid)
INNER JOIN clients ON events.clientid = clients.clientid)
INNER JOIN djemployee ON events.assigned_dj = djemployee.djemployeeid

OR this:
SELECT events.*, venuedb.name AS venue_name, venuedb.city AS venue_city, clients.organization,
       clients.first_name+' '+clients.last_name AS client_name, client_type,
        (events.assigned_dj_fee + events.assigned_dj2_fee + events.assigned_dj3_fee + events.assigned_dj4_fee + events.assigned_dj5_fee) AS event_employee_wages
FROM (
(events INNER JOIN venuedb ON events.event_location_id = venuedb.venueid)
INNER JOIN clients ON events.clientid = clients.clientid)
INNER JOIN djemployee ON events.assigned_dj = djemployee.djemployeeid
0
 
LVL 22

Expert Comment

by:JimBrandley
ID: 19664231
Also, you do not seem to be selecting anything from djemployee, so I'm not sure whay that's in the join.

As far as performance goes, getting it all in one select will be faster than two, even though the addition and extra returned will take a bit longer.

Jim
0
 
LVL 44

Assisted Solution

by:GRayL
GRayL earned 1000 total points
ID: 19664402
Don't use sum() to sum across a record - that appears to be what you are doing.  Sum() is only used to get the total of columns.  How does this work?

SELECT events.*, venuedb.name AS venue_name, venuedb.city AS venue_city, clients.organization, clients.first_name+' '+clients.last_name AS client_name, client_type, (events.assigned_dj_fee + events.assigned_dj2_fee + events.assigned_dj3_fee + events.assigned_dj4_fee + events.assigned_dj5_fee) AS event_employee_wages
FROM (
(events INNER JOIN venuedb ON events.event_location_id = venuedb.venueid)
INNER JOIN clients ON events.clientid = clients.clientid)
INNER JOIN djemployee ON events.assigned_dj = djemployee.djemployeeid
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 44

Expert Comment

by:GRayL
ID: 19664428
Also, in Access you should use the & to concatenate strings, not +:

SELECT events.*, venuedb.name AS venue_name, venuedb.city AS venue_city, clients.organization, clients.first_name & ' ' & clients.last_name AS client_name, client_type, (events.assigned_dj_fee + events.assigned_dj2_fee + events.assigned_dj3_fee + events.assigned_dj4_fee + events.assigned_dj5_fee) AS event_employee_wages
FROM (
(events INNER JOIN venuedb ON events.event_location_id = venuedb.venueid)
INNER JOIN clients ON events.clientid = clients.clientid)
INNER JOIN djemployee ON events.assigned_dj = djemployee.djemployeeid
0
 
LVL 1

Author Comment

by:djlurch
ID: 19664600
Jim:

Here is the error I get when I copy and paste your code: Syntax error (missing operator) in query expression "events.assigned_dj = djemployee.djemployeeid ..... OR this: SELECT events.*'.

The SQL statement is generic and is used elsewhere. I do some manual field replacements, and that is why the djemployeeid is in there.

GRayL:

Nice. It works. I was copy/pasting from another query that I created and forgot about the proper usage of SUM. I only need to generate new non-trivial SQL statements once every few weeks and I get rusty pretty fast on them.



0
 
LVL 22

Expert Comment

by:JimBrandley
ID: 19664733
That was two different selects, separated by Or This:. The second one posted by GRayL should be good to go.

Jim
0
 
LVL 1

Author Comment

by:djlurch
ID: 19664954
Jim:

<embarassed> I see that now <embarassed>

I can confirm that your second query worked. I used GRayL's post to fix it but you posted first. I hope you both don't mind if I split up the points.

Thank you both of you!!!
0
 
LVL 22

Expert Comment

by:JimBrandley
ID: 19664992
No worries - glad it worked for you.

Jim
0
 
LVL 44

Expert Comment

by:GRayL
ID: 19665006
Thanks, glad to help.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question