SQLTeam.com | Weblogs | Forums

Something wrong with my query- new to sql

SELECT eq.emp3,eq.wages,
CASE WHEN eq.ave_wages = 0 then '0'
WHEN eq.ave_wages between 0 AND 500 then '0-500'
WHEN eq.ave_wages between 501 and 1000 then '501-1000'
WHEN eq.ave_wages between 1001 and 1500 then '1001-1500'
WHEN eq.ave_wages between 1501 and 2000 then '1501-2000'
WHEN eq.ave_wages between 2001 and 2500 then '2001-2500'
WHEN eq.ave_wages between 2501 and 3000 then '2501-3000'
WHEN eq.ave_wages between 3001 and 3500 then '3001-3500'
WHEN eq.ave_wages between 3501 and 4000 then '3501-4000'
WHEN eq.ave_wages between 4001 and 4500 then '4001-4500'
WHEN eq.ave_wages between 4501 and 5000 then '4501-5000'
WHEN eq.ave_wages between 5001 and 5500 then '5001-5500'
WHEN eq.ave_wages between 5501 and 6000 then '5501-6000'
WHEN eq.ave_wages between 6001 and 6500 then '6001-6500'
WHEN eq.ave_wages between 6501 and 7000 then '6501-7000'
WHEN eq.ave_wages between 7001 and 7500 then '7001-7500'
WHEN eq.ave_wages >= 7500 then '7500+' end wage_range,
from
(select distinct eq.emp3,eq.wages, sum(eq.emp1)emp1,sum(eq.emp2)emp2,
sum(eq.emp3)emp3,sum(eq.wages)wages,round(avg(emp1 + emp2 + emp3)/wages) ave_wages
FROM b_owner._eqi eq
WHERE eq.yrqtr = 20181
and eq.meei != 2)

Any tips?

You have a few syntax errors, which I have fixed below. If the query does not give you the right results, the problem may be in the logic.

SELECT eq.emp3,
       eq.wages,
       CASE
           WHEN eq.ave_wages = 0 THEN
               '0'
           WHEN eq.ave_wages
                BETWEEN 0 AND 500 THEN
               '0-500'
           WHEN eq.ave_wages
                BETWEEN 501 AND 1000 THEN
               '501-1000'
           WHEN eq.ave_wages
                BETWEEN 1001 AND 1500 THEN
               '1001-1500'
           WHEN eq.ave_wages
                BETWEEN 1501 AND 2000 THEN
               '1501-2000'
           WHEN eq.ave_wages
                BETWEEN 2001 AND 2500 THEN
               '2001-2500'
           WHEN eq.ave_wages
                BETWEEN 2501 AND 3000 THEN
               '2501-3000'
           WHEN eq.ave_wages
                BETWEEN 3001 AND 3500 THEN
               '3001-3500'
           WHEN eq.ave_wages
                BETWEEN 3501 AND 4000 THEN
               '3501-4000'
           WHEN eq.ave_wages
                BETWEEN 4001 AND 4500 THEN
               '4001-4500'
           WHEN eq.ave_wages
                BETWEEN 4501 AND 5000 THEN
               '4501-5000'
           WHEN eq.ave_wages
                BETWEEN 5001 AND 5500 THEN
               '5001-5500'
           WHEN eq.ave_wages
                BETWEEN 5501 AND 6000 THEN
               '5501-6000'
           WHEN eq.ave_wages
                BETWEEN 6001 AND 6500 THEN
               '6001-6500'
           WHEN eq.ave_wages
                BETWEEN 6501 AND 7000 THEN
               '6501-7000'
           WHEN eq.ave_wages
                BETWEEN 7001 AND 7500 THEN
               '7001-7500'
           WHEN eq.ave_wages >= 7500 THEN
               '7500+'
       END wage_range
FROM
	(SELECT DISTINCT
		   eq.emp3,
		   eq.wages,
		   SUM(eq.emp1) emp1,
		   SUM(eq.emp2) emp2,
		   SUM(eq.emp3) emp3,
		   SUM(eq.wages) wages,
		   ROUND(AVG(emp1 + emp2 + emp3) / wages,0,0) ave_wages
	FROM b_owner._eqi eq
	WHERE eq.yrqtr = 20181
		  AND eq.meei != 2) AS s;

Thanks, I tried and it said invalid ending. I tried this and I get the error that this has an invalid identifier with eq.ave_wage

SELECT eq.emp3, eq.wages, eq.ave_wages,
CASE WHEN EQ.ave_wages = 0 THEN '0'
WHEN EQ.ave_wages BETWEEN 0 AND 500 THEN '0-500'
WHEN EQ.ave_wages BETWEEN 501 AND 1000 THEN '501-1000'
WHEN EQ.ave_wages BETWEEN 1001 AND 1500 THEN '1001-1500'
WHEN EQ.ave_wages BETWEEN 1501 AND 2000 THEN '1501-2000'
WHEN EQ.ave_wages BETWEEN 2001 AND 2500 THEN '2001-2500'
WHEN EQ.ave_wages BETWEEN 2501 AND 3000 THEN '2501-3000'
WHEN EQ.ave_wages BETWEEN 3001 AND 3500 THEN '3001-3500'
WHEN EQ.ave_wages BETWEEN 3501 AND 4000 THEN '3501-4000'
WHEN EQ.ave_wages BETWEEN 4001 AND 4500 THEN '4001-4500'
WHEN EQ.ave_wages BETWEEN 4501 AND 5000 THEN '4501-5000'
WHEN EQ.ave_wages BETWEEN 5001 AND 5500 THEN '5001-5500'
WHEN EQ.ave_wages BETWEEN 5501 AND 6000 THEN '5501-6000'
WHEN EQ.ave_wages BETWEEN 6001 AND 6500 THEN '6001-6500'
WHEN EQ.ave_wages BETWEEN 6501 AND 7000 THEN '6501-7000'
WHEN EQ.ave_wages BETWEEN 7001 AND 7500 THEN '7001-7500'
WHEN EQ.ave_wages >= 7500 THEN'7500+' END wage_range
FROM (SELECT DISTINCT eq.emp3,eq.wages,SUM(eq.emp1) emp1,SUM(eq.emp2) emp2,
SUM(eq.emp3) emp3, SUM(eq.wages) wages,ROUND(AVG(emp1 + emp2 + emp3) / wages,0,0) ave_wage
FROM owner.b__equ eq
WHERE eq.yrqtr = 20181
AND eq.meei != 2)

hi

i tried to do this .. hope this helps :slight_smile: :slight_smile:

please click arrow to the left for "drop create sample data SCRIPT"
drop table owner.qui 
go 

drop schema owner 
go 

create schema owner
go 


create table owner.qui
(
emp3 int,
wages int,
emp1 int,
emp2 int,
yrqtr int,
meei int 
)
go 

insert into owner.qui select 10,100,20,45,20181,3
insert into owner.qui select 20,150,15,50,20181,4
insert into owner.qui select 7 ,55, 28,23,20181,5
go 

select 'sample data',* from owner.qui 
go

i was getting syntax errors .. so i fixed them like this
-- added group by clause ..
-- round requires 2 to 3 arguments .. fixed that

please click arrow to the left for "SQL"
SELECT DISTINCT eq.emp3, 
                eq.wages, 
                Sum(eq.emp1)                              emp1, 
                Sum(eq.emp2)                              emp2, 
                Sum(eq.emp3)                              emp3, 
                Sum(eq.wages)                             wages, 
                Round(Avg(emp1 + emp2 + emp3 / wages), 5) ave_wage 
FROM   owner.qui eq 
WHERE  eq.yrqtr = 20181 
       AND eq.meei != 2 
GROUP  BY eq.emp3, 
          eq.wages, 
          eq.emp1, 
          eq.emp2