Solved

SQL question

Posted on 2008-10-19
15
192 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
  • 7
  • 5
  • 2
  • +1
15 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
Comment Utility
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
Comment Utility
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
Comment Utility
(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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
Comment Utility
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
Comment Utility
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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 1

Author Comment

by:billium99
Comment Utility
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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
>> 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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
>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
Comment Utility
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
PERFORMANCE OF SQL QUERY 13 58
Pivot Query Problem 9 30
Grid querry results 41 51
Oracle TEXT search question 9 26
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now