Solved

SQL required to group by range . . .

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SSRS 2013 - Overlapping reports 2 22
Sql Server group by 10 27
always on switch back after failover 2 34
SQL View nearest date 5 36
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
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.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

773 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