why my followning sql query doesnt work

Question

select 
    'N' as Content,  
    (select COUNT(*)  from TB1  ) as [Sample Analyzed ],  
    (select COUNT(*)  from TB1  where convert(float,D) between 0.0005 and 0.75) as [Very Low],  
    (select COUNT(*)  from TB1  where convert(float,D) between 0.76 and 1.25 ) as [Low] 
union  
    select 'P' as Content,  
    (select COUNT(*)  from TB1  ) as [Sample Analyzed ],  
    (select COUNT(*)  from TB1  where convert(float,C) between 0.0005 and 0.75) as [Very Low],  
    (select COUNT(*)  from TB1  where convert(float,C) between 0.76 and 1.25 ) as [Low] 
union  
    select 'K' as Content,  
    (select COUNT(*)  from TB1  ) as [Sample Analyzed ],
    (select COUNT(*)  from TB1  where convert(float,B) between 0.0005 and 0.75) as [Very Low],  
    (select COUNT(*)  from TB1  where convert(float,B) between 0.76 and 1.25 ) as [Low] 
union  
    select 'OC' as Content,  
    (select COUNT(*)  from TB1  ) as [Sample Analyzed ],  
    (select COUNT(*)  from TB1  where convert(float,A) between 0.0005 and 0.75) as [Very Low],  
    (select COUNT(*)  from TB1  where convert(float,A) between 0.76 and 1.25 ) as [Low]
ORDER BY
    (
     CASE 
       WHEN  Content = 'N' THEN 0
       WHEN  Content = 'P' THEN 1
       WHEN  Content = 'K' THEN 2
       WHEN  Content = 'OC' THEN 3
       ELSE 4
     END
    )

Define "doesn't work"? What errors do you get? What is the expected output? Read this: stackoverflow.com/help/mcve — S3S
– S3S, Commented Aug 21, 2017 at 14:32
Msg 207, Level 16, State 1, Procedure Get_NPK_Report, Line 44 Invalid column name 'Content'. Msg 207, Level 16, State 1, Procedure Get_NPK_Report, Line 44 Invalid column name 'Content'. Msg 207, Level 16, State 1, Procedure Get_NPK_Report, Line 44 Invalid column name 'Content'. Msg 207, Level 16, State 1, Procedure Get_NPK_Report, Line 44 Invalid column name 'Content'. Msg 104, Level 16, State 1, Procedure Get_NPK_Report, Line 50 ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator. — MANGESH UKADE
– MANGESH UKADE, Commented Aug 21, 2017 at 14:33
above error i got in sql serve r2008 my output now K 7 0 1 N 7 1 1 OC 7 0 0 P 7 0 2 what i want i want order of K,N,OC,P should be like N,P,K,OC — MANGESH UKADE
– MANGESH UKADE, Commented Aug 21, 2017 at 14:34
Ok? The error is clear and I've given a way to circumvent it. — S3S
– S3S, Commented Aug 21, 2017 at 14:41
Please note that additional information that is important to understand the problem, should be included in the question itself, not in the comment section. You can always edit your question and add the information in the question itself. — TT.
– TT., Commented Aug 21, 2017 at 14:53

S3S · Accepted Answer · 2017-08-21 14:44:04Z

...Invalid column name 'Content'....

That's because you can't use a column alias in the ORDER BY

ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator

That's because the column alias isn't part of the select, same as above. It isn't an actual column (yet)

Here's an example by wrapping it in a CTE.

with cte as(
select 
    'N' as Content,  
    (select COUNT(*)  from TB1  ) as [Sample Analyzed ],  
    (select COUNT(*)  from TB1  where convert(float,D) between 0.0005 and 0.75) as [Very Low],  
    (select COUNT(*)  from TB1  where convert(float,D) between 0.76 and 1.25 ) as [Low] 
union  
    select 'P' as Content,  
    (select COUNT(*)  from TB1  ) as [Sample Analyzed ],  
    (select COUNT(*)  from TB1  where convert(float,C) between 0.0005 and 0.75) as [Very Low],  
    (select COUNT(*)  from TB1  where convert(float,C) between 0.76 and 1.25 ) as [Low] 
union  
    select 'K' as Content,  
    (select COUNT(*)  from TB1  ) as [Sample Analyzed ],
    (select COUNT(*)  from TB1  where convert(float,B) between 0.0005 and 0.75) as [Very Low],  
    (select COUNT(*)  from TB1  where convert(float,B) between 0.76 and 1.25 ) as [Low] 
union  
    select 'OC' as Content,  
    (select COUNT(*)  from TB1  ) as [Sample Analyzed ],  
    (select COUNT(*)  from TB1  where convert(float,A) between 0.0005 and 0.75) as [Very Low],  
    (select COUNT(*)  from TB1  where convert(float,A) between 0.76 and 1.25 ) as [Low]
)

select * from cte 
ORDER BY
    (
     CASE 
       WHEN  Content = 'N' THEN 0
       WHEN  Content = 'P' THEN 1
       WHEN  Content = 'K' THEN 2
       WHEN  Content = 'OC' THEN 3
       ELSE 4
     END
    )

Collectives™ on Stack Overflow

why my followning sql query doesnt work

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related