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

SQL question

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
billium99
Asked:
billium99
  • 7
  • 5
  • 2
  • +1
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
billium99Author Commented:
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
 
billium99Author Commented:
(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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
dqmqCommented:
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
 
billium99Author Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
billium99Author Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
billium99Author Commented:
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
 
Jason C. LevineNo oneCommented:
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
 
billium99Author Commented:
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
 
Jason C. LevineNo oneCommented:
>> 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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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
 
billium99Author Commented:
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 7
  • 5
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now