Solved

SQL question

Posted on 2008-10-19
15
196 Views
Last Modified: 2012-05-05
Hi,

I have two tables - Jobs and Users.

Jobs contains all the records and $ values of each sale.

Users contains the reference to User name and UserID that I use to associate the right user name with the record in Jobs (which only keeps the userID).

I hope to have a query that SUMS and AVGs each User's sales figures, but I can't get the SQL right. My current attempt includes a bunch of Sort Bys which it appears I have to have present if I'm using SUM or AVG.

Here is my current SQL and it's only giving me the first record, with no sum or average other than just showing that first Job's $ amount:

SELECT SUM(Jobs.AcceptedPrice) AS TOTAL, AVG(Jobs.AcceptedPrice) AS AVERAGE, Jobs.JobID, Jobs.EstimatorAssigned, Jobs.EarliestEstimateDateTime, Users.UserID, Users.UserFirstName
FROM Jobs RIGHT JOIN Users ON Jobs.EstimatorAssigned=Users.UserID
WHERE Users.UserType=2 GROUP BY Jobs.EstimatorAssigned, Jobs.EarliestEstimateDateTime, Users.UserID, Users.UserFirstName, Jobs.JobID

I was hoping my final looped array (a DREAMWEAVER recordset so I have add other functions) would show the first user name, his total, and his average, then move to the next unique username in the list and show the same thing.

I'm keeping the date in there so I can run search queries that filter this recordset by date range (hence the need for a standard DW recordset)

Any suggestions?

Thank you

What am I doing wrong here?

Thanks
0
Comment
Question by:billium99
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 2
  • +1
15 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
ID: 22753368
you cannot really get the SUM/AVERAGE of values in the JOBS table at the same time than the details. does not make any "sense" in terms of SQL:

SELECT SUM(Jobs.AcceptedPrice) AS TOTAL, AVG(Jobs.AcceptedPrice) AS AVERAGE, Users.UserID, Users.UserFirstName
FROM Jobs RIGHT JOIN Users ON Jobs.EstimatorAssigned=Users.UserID
WHERE Users.UserType=2 GROUP BY Users.UserID, Users.UserFirstName

Open in new window

0
 
LVL 1

Author Comment

by:billium99
ID: 22753409
OK, so is there no way to add the date field for later sorting - in other words, I want to have sums and averages based on a date range. My date/time search query requires a standard DW recordset, which your new query works perfectly for (except that I can't filter your query on a date range)

Thanks for your time!

Bill
0
 
LVL 1

Author Comment

by:billium99
ID: 22753423
(PS - when I run a standard repeat region on your queries bound values, it works exactly as I hope, except that it's all records.

Could we introduce a new table field in the WHERE clause with no issues?

WHERE Users.UserType=2 AND Jobs.EarliestEstimateDateTime=MM_ColParam

and then MM_ColParam becomes the values passed by the search query? This would probably work if the search query returned a single date...

Not sure what the best path forward is here.

Thanks

Bill
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22753444
you could use MIN(Jobs.EarliestEstimateDateTime) and MAX(Jobs.EarliestEstimateDateTime) in your query, for example? ...
I don't know what you really need, from those dates in the end results...
0
 
LVL 42

Assisted Solution

by:dqmq
dqmq earned 250 total points
ID: 22753449
Angel3 is right, but you can compute your sum/average in one select and then combine it back to the details:

 

;With Sums
as
(SELECT SUM(Jobs.AcceptedPrice) AS TOTAL, AVG(Jobs.AcceptedPrice) AS AVERAGE, Users.UserID, Users.UserFirstName
FROM Jobs
GROUP BY Users.UserID, Users.UserFirstName
)
SELECT Jobs.EstimatorAssigned, Jobs.EarliestEstimateDateTime, Count(*) JobCount, Sums.TOTAL, Sums.AVERAGE, Sums.UserID, Sums.UserFirstName
FROM Jobs inner join Sums on Jobs.UserId=Sums.UserId
WHERE Users.UserType=2
Group By TOTAL, AVERAGE, Sums.UserID, Sums.UserFirstName,
Jobs.EstimatorAssigned, Jobs.EarliestEstimateDateTime

Open in new window

0
 
LVL 1

Author Comment

by:billium99
ID: 22753485
Thanks,

But what is the syntax? If I copy and paste what you have above it's telling me I need a SELECT statement. Do I add the ";With Sums as" somehow?

Bill
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22753505
yes, that
;WITH SUMS AS

is part of the query, it's new in sql 2005.
I will edit the comment above and move the code into a code snippet to make it clearer.
0
 
LVL 1

Author Comment

by:billium99
ID: 22753659
DW says I must enter a SELECT statement or a call to a procedure that returns a recordset when I use the exact syntax above
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22753827
well, DW might not know (yet) about such syntax, so you should put that into a stored procedure (recommended anyhow), and just pass the parameters as needed.
0
 
LVL 1

Author Comment

by:billium99
ID: 22753852
Sorry - is a stored procedure run inside the db?

This is a .mdb file for use on our Windows 2003 server.

To support this functionality it sounds like I need a newer version of IIS or ODBC? And convert my .mdb file into .accdb? Then add the code above as a new query?

Bill
0
 
LVL 70

Expert Comment

by:Jason C. Levine
ID: 22753955
FYI to Experts and billium99,

Dreamweaver's code writing wizards for MS SQL/MY SQL support are pretty primitive and there's a lot can you can do via hand-coding that you cannot do via the built-in Wizards.  So the price of increased functionality is you lose the ability to have Dreamweaver recognize the recordsets and bind the columns to the Panels.  
0
 
LVL 1

Author Comment

by:billium99
ID: 22754676
Hi Jason,

Can I translate your message to mean that I shouldn't seek help within my DW framework? I acknowledge that I need to graduate beyond this platform, but right now? In the middle of this gig I'm doing? Sorry - I gotta try to make this work with my limited tools and knowledge.

Would it help if I pasted the current recordset as it appears in the resulting code?

<%
Dim summary
Dim summary_cmd
Dim summary_numRows

Set summary_cmd = Server.CreateObject ("ADODB.Command")
summary_cmd.ActiveConnection = MM_mande_STRING
summary_cmd.CommandText = "SELECT JobID, EstimatorAssigned, EarliestEstimateDateTime, AcceptedPrice FROM Jobs"
summary_cmd.Prepared = true

Set summary = summary_cmd.Execute
summary_numRows = 0
%>

Is there a straight forward way to translate the code in the thread above to work in this syntax? (A flat copy/paste isn't doing it)

Thanks for your time

Bill
0
 
LVL 70

Expert Comment

by:Jason C. Levine
ID: 22754917
>> Can I translate your message to mean that I shouldn't seek help within my DW framework?

No.  The point I'm trying to get across is that you will eventually run into limits with what DW can do for you via the automated stuff and you will need to switch over to hand-coding at that point.  It can still be used as an editor but constructing advanced queries is problematic.

>> Is there a straight forward way to translate the code in the thread above to work in this syntax? (A flat
>> copy/paste isn't doing it)

Try creating a new recordset and pasting the code into the Advanced view and see what happens.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22755185
>Sorry - is a stored procedure run inside the db?
>This is a .mdb file for use on our Windows 2003 server.

well, you had posted under ms sql server 2005, which is not the same as ms access ...
ms access does not support neither the WITH syntax, nor "stored procedures" as such.

please clarify.
0
 
LVL 1

Author Comment

by:billium99
ID: 22755372
OMG - I didn't catch that - must've mic-clixked on category selection - sorry guys.

I guess I'll re-ask the question in the right zone...
0

Featured Post

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …

730 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