Solved

SQL required to group by range . . .

Posted on 2004-04-21
5
1,746 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
[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
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

688 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