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
Solved

SQL required to group by range . . .

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
T-SQL 10 35
t-sql need help on t-sql 10 25
SQL create line numbers for data sampling 11 27
Substring works but need to tweak it 14 12
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

840 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