none
More elegant way of writing this query w/o using CTE and variables?

    Question

  • Each CTE returns two values: I divide one by the other and I get a percentage.

    The query works, but I don't think I need a cte or variables for this.

    So how can this look more elegant?

    Thanks.

    ;with cte2 as (
    	select FIELD1 From TABLE1 where FIELD2 not like 'ABC%' and FIELD1 not in (
    	select FIELD1 From TABLE1 where FIELD2 like 'ABC%'
    	group by FIELD1 )
    	group by FIELD1
    ) select count(*) from cte2
    
    ;with cte as (
    	select FIELD1 From TABLE1 group by FIELD1
    )
    select count(*) from cte
    



    VM

    Wednesday, July 24, 2013 7:56 PM

Answers

  • No CTE, No Variable and No DISTINCT keyword, 

    select 
    cast((select count(*) from (select FIELD1 From TABLE1 where FIELD2 not like 'ABC%' and FIELD1 not in (
    	select FIELD1 From TABLE1 where FIELD2 like 'ABC%'
    	group by FIELD1 )
    	group by FIELD1) b) as decimal)
    /
    cast((select count(*) from (select FIELD1 From TABLE1 group by FIELD1) a) as decimal) as DivValue


    Regards, RSingh


    • Edited by RSingh() Thursday, July 25, 2013 4:20 AM add txt
    • Marked as answer by vmhatup Friday, July 26, 2013 3:24 PM
    Thursday, July 25, 2013 4:18 AM

All replies

  • Can you provide your table DDL and some sample data and expected result? Thanks.
    Wednesday, July 24, 2013 8:02 PM
    Moderator
  • I don't think data was needed since it's pretty simple, but here goes:

    FIELD1   FIELD2
    1             ABC
    1             XYZ
    2             ABC
    2             XYZ
    3             XYZ
    The cte2 will return 1, the cte will return 3. 1/3 = .3333

    The table only has two  fields: FIELD1, FIELD2


    VM


    • Edited by vmhatup Wednesday, July 24, 2013 8:18 PM modify post
    Wednesday, July 24, 2013 8:18 PM
  • create table TABLE1 (FIELD1  int, FIELD2 varchar(50))
    Insert into TABLE1 values (1,'ABC'),(1,'XYZ'),(2,'ABC'),(2,'XYZ'),(3,'XYZ')
    select * from TABLE1
     
    select (SELECT Count(distinct FIELD1) From TABLE1 where FIELD2 not like 'ABC%'   and  FIELD1 NOT IN (Select distinct FIELD1 From TABLE1 where FIELD2 like 'ABC%')) *1.
    / count(distinct FIELD1)  
    From table1  
    drop table TABLE1

    Wednesday, July 24, 2013 8:39 PM
    Moderator
  • Thanks.

    I was hoping to use, possibly, a left outer join and an alias. Since there's a good amount of data, I prefer not using distinct keyword.


    VM

    Wednesday, July 24, 2013 8:56 PM
  • No CTE, No Variable and No DISTINCT keyword, 

    select 
    cast((select count(*) from (select FIELD1 From TABLE1 where FIELD2 not like 'ABC%' and FIELD1 not in (
    	select FIELD1 From TABLE1 where FIELD2 like 'ABC%'
    	group by FIELD1 )
    	group by FIELD1) b) as decimal)
    /
    cast((select count(*) from (select FIELD1 From TABLE1 group by FIELD1) a) as decimal) as DivValue


    Regards, RSingh


    • Edited by RSingh() Thursday, July 25, 2013 4:20 AM add txt
    • Marked as answer by vmhatup Friday, July 26, 2013 3:24 PM
    Thursday, July 25, 2013 4:18 AM
  • Give a try,

    select (
    select COUNT(*) From TABLE1 A where FIELD2 not like 'ABC%' 
    and  not exists (
    	select FIELD1 From TABLE1 B where B.FIELD2 like 'ABC%' and B.FIELD1=A.FIELD1
    	 )
    	 group by FIELD1)/(select count(*)*1.0 from (select FIELD1 From TABLE1 group by FIELD1)C)


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thursday, July 25, 2013 5:32 AM
  • I see two queries, which one  you use? 

    Second one could be rewritten as below

    select count(*)  cn,FIELD1 From TABLE1 group by FIELD1



    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Thursday, July 25, 2013 5:54 AM
    Answerer