Solved

SQL required to group by range . . .

Posted on 2004-04-21
5
1,738 Views
Last Modified: 2012-05-04
Hi There,

I have the following working query:

select Count(CandidatetoJobID) as ThisCount,j.HourlyRate
from FSU_CandidatetoJob ctoj, FSU_Job j
where ctoj.JobID = j.jobID
group by j.HourlyRate

It returns one record for each unique hourly rate with a count of the number of jobs with that hourly rate in the ThisCount "field".

What I need is to be able to get the same query, but with one record for each RANGE of hourly rates. For example, first record should be for $5-$8/hr, second for $8.01 - $10/hr, third for $10.01 - $100/hr. Hourly rate is a decimal field, so ranges are 5-8, 8.01-10 and 10.01-100.

Any help appreciated!

Best Wishes,
Peter




0
Comment
Question by:freshstartusa
5 Comments
 
LVL 34

Expert Comment

by:arbert
ID: 10882004
You will have to use a case statement to do that.  This should be a start.

select Count(CandidatetoJobID) as ThisCount,
case when j.HourlyRate between 5 and 8 then '$5-$8'
        when j.HourlyRate between 8.01 and 10 then '$8.01-$10.00' end as TheGroup

from FSU_CandidatetoJob ctoj, FSU_Job j
where ctoj.JobID = j.jobID
group by

case when j.HourlyRate between 5 and 8 then '$5-$8'
        when j.HourlyRate between 8.01 and 10 then '$8.01-$10.00'
0
 

Author Comment

by:freshstartusa
ID: 10882183
Hi Arbert,

When I use the query you posted, I got the following error. Any ideas?

Any help appreciated!
Peter

[Microsoft][ODBC SQL Server Driver][SQL Server]Column 'j.HourlyRate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

SQL = "select Count(CandidatetoJobID) as ThisCount,case when j.HourlyRate between 5 and 8 then '$5-$8' when j.HourlyRate between 8.01 and 10 then '$8.01-$10.00' end as TheGroup,j.HourlyRate from FSU_CandidatetoJob ctoj, FSU_Job j where ctoj.JobID = j.jobID and ctoj.Status = 'Hired' and ( ctoj.AssociatedDateTime between '1/1/2004 0:00:00' and '1/31/2004 23:59:59' ) group by case when j.HourlyRate between 5 and 8 then '$5-$8' when j.HourlyRate between 8.01 and 10 then '$8.01-$10.00' end"
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 10882212
Please try the query below (sorry, don't have a way to test it first).  
I suggest you make the ranges (called "rates" below) a separate table of its own: that is much more flexible and easier to change the ranges.
P.S. I changed the existing join to use the new ANSI syntax ... hope you don't mind :-).


SELECT Count(ctoj.CandidatetoJobID) as ThisCount,
      '$' + STR(rates.Low, 5, 2) + ' - $' + STR(rates.High, 6, 2)
FROM FSU_CandidatetoJob ctoj
INNER JOIN FSU_Job j ON ctoj.JobID = j.jobID
INNER JOIN (
      SELECT 5.00 AS Low,  8.00 AS High
      UNION ALL
      SELECT 8.01, 10.00
      UNION ALL
      SELECT 10.01, 100.00
) AS rates ON j.HourlyRate BETWEEN rates.Low AND rates.High
GROUP BY '$' + STR(rates.Low, 5, 2) + ' - $' + STR(rates.High, 6, 2)
0
 
LVL 34

Accepted Solution

by:
arbert earned 175 total points
ID: 10882652
If you're going to include j.HourlyRate in the select, you must also include it in the group by.  Since you have this field in the range, I don't think you want it on the select.


select Count(CandidatetoJobID) as ThisCount,case when j.HourlyRate between 5 and 8 then '$5-$8' when j.HourlyRate between 8.01 and 10 then '$8.01-$10.00' end as TheGroup from FSU_CandidatetoJob ctoj, FSU_Job j where ctoj.JobID = j.jobID and ctoj.Status = 'Hired' and ( ctoj.AssociatedDateTime between '1/1/2004 0:00:00' and '1/31/2004 23:59:59' ) group by case when j.HourlyRate between 5 and 8 then '$5-$8' when j.HourlyRate between 8.01 and 10 then '$8.01-$10.00' end, j.HourlyRate
0
 

Expert Comment

by:kochar
ID: 20189837
Here is my solution using a temp table. This query is not specific to your question but will give you an idea.

Declare @Range as Int
Set @Range = 0
Declare @RangeTable Table(RangeFrom int, RangeTo Int)
While @Range < 100000000 begin
      Insert Into @RangeTable
      Select @Range + 1, @Range + 100000
      Set @Range = @Range + 100000
End
-- Select * from @RangeTable
Select DC.DivisionName, RT.RangeFrom, RT.RangeTo -- , Min(C.RequisitionNum) as MinRequisitionNum, Max(C.RequisitionNum) as MinRequisitionNum
from QE2_Prod.dbo.Cases C
Inner Join Divisions DC on DC.DivisionID = C.DivisionID
Inner Join @RangeTable RT on C.RequisitionNum between RT.RangeFrom and RT.RangeTo
Group By DC.DivisionName, RT.RangeFrom, RT.RangeTo
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

863 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

19 Experts available now in Live!

Get 1:1 Help Now