Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL required to group by range . . .

Posted on 2004-04-21
5
Medium Priority
?
1,757 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 70

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 700 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

926 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