Something wrong with my query- new to sql

alyb · November 6, 2019, 7:20pm

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?

JamesK · November 6, 2019, 9:01pm

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;

alyb · November 7, 2019, 5:40pm

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)

harishgg1 · November 9, 2019, 10:08am

hi

i tried to do this .. hope this helps

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