locked
Select within a Select statement RRS feed

  • Question

  • I have created a select statement which gathers a number of columns from a table and does a few calculations.

     

    How can I create another select statement using my initial select statement...

     

    So that’s a select within a select

    Monday, June 15, 2009 10:57 AM

Answers

  • It is not completely clear what you want, but perhaps something like:

    select
      CASE

       WHEN [Therapy service] = 'Upper Limb Therapy'

       THEN 'Occupational Therapy'

       ELSE [Therapy service] END AS [Therapy service New],
     count(*) as [New Tharapy Service Count]
    from TableTest
    group by
      CASE

       WHEN [Therapy service] = 'Upper Limb Therapy'

       THEN 'Occupational Therapy'

       ELSE [Therapy service] END


    Another possiblity might be something like:

    select
      [Therapy Service],
      CASE

       WHEN [Therapy service] = 'Upper Limb Therapy'

       THEN 'Occupational Therapy'

       ELSE [Therapy service] END AS [Therapy service New],
     count(*) as [New Tharapy Service Count]
    from TableTest
    group by
      [Therapy Service],
      CASE

       WHEN [Therapy service] = 'Upper Limb Therapy'

       THEN 'Occupational Therapy'

       ELSE [Therapy service] END




    Kent Waldrop
    • Marked as answer by akhlaq768 Monday, June 15, 2009 2:00 PM
    Monday, June 15, 2009 1:36 PM

All replies

  • Look into "Derived Tables" and "Common Table Expressions" or "CTEs"
    Kent Waldrop
    Monday, June 15, 2009 11:09 AM
  • Here is a sample for Kent's suggestion.
    First query is using a CTE expression. Second is using a select as a table.

    Eralper
    http://www.kodyaz.com


    with cte as
    (
    	select
    		wd = DATEPART(WEEK,date),
    		DATE,
    		Sunday = case when days = 'Sunday' then date else null end,
    		Monday = case when days = 'Monday' then date else null end,
    		Tuesday = case when days = 'Tuesday' then date else null end,
    		Wednesday = case when days = 'Wednesday' then date else null end,
    		Thursday = case when days = 'Thursday' then date else null end,
    		Friday = case when days = 'Friday' then date else null end,
    		Saturday = case when days = 'Saturday' then date else null end
    	from dbo.getFullmonth (GETDATE())
    )
    select
    	MAX(Sunday) Sunday,
    	MAX(Monday) Monday,
    	MAX(Tuesday) Tuesday,
    	MAX(Wednesday) Wednesday,
    	MAX(Thursday) Thursday,
    	MAX(Friday) Friday,
    	MAX(Saturday) Saturday
    from cte
    group by wd
    
    
    select
    	MAX(Sunday) Sunday,
    	MAX(Monday) Monday,
    	MAX(Tuesday) Tuesday,
    	MAX(Wednesday) Wednesday,
    	MAX(Thursday) Thursday,
    	MAX(Friday) Friday,
    	MAX(Saturday) Saturday
    from (
    	select
    		wd = DATEPART(WEEK,date),
    		DATE,
    		Sunday = case when days = 'Sunday' then date else null end,
    		Monday = case when days = 'Monday' then date else null end,
    		Tuesday = case when days = 'Tuesday' then date else null end,
    		Wednesday = case when days = 'Wednesday' then date else null end,
    		Thursday = case when days = 'Thursday' then date else null end,
    		Friday = case when days = 'Friday' then date else null end,
    		Saturday = case when days = 'Saturday' then date else null end
    	from dbo.getFullmonth (GETDATE())
    ) cte
    group by wd
    


    http://www.kodyaz.com http://www.eralper.com
    Monday, June 15, 2009 11:31 AM
  •       SELECT

                [Therapy service]

                ,CASE

                      WHEN [Therapy service] = 'Upper Limb Therapy'

                      THEN 'Occupational Therapy'

                      ELSE [Therapy service] END AS [Therapy service New]

                FROM TableTest

     

    How do I create a another select statement that Groups and Count the [Therapy Service New] column?

    Monday, June 15, 2009 1:20 PM
  • It is not completely clear what you want, but perhaps something like:

    select
      CASE

       WHEN [Therapy service] = 'Upper Limb Therapy'

       THEN 'Occupational Therapy'

       ELSE [Therapy service] END AS [Therapy service New],
     count(*) as [New Tharapy Service Count]
    from TableTest
    group by
      CASE

       WHEN [Therapy service] = 'Upper Limb Therapy'

       THEN 'Occupational Therapy'

       ELSE [Therapy service] END


    Another possiblity might be something like:

    select
      [Therapy Service],
      CASE

       WHEN [Therapy service] = 'Upper Limb Therapy'

       THEN 'Occupational Therapy'

       ELSE [Therapy service] END AS [Therapy service New],
     count(*) as [New Tharapy Service Count]
    from TableTest
    group by
      [Therapy Service],
      CASE

       WHEN [Therapy service] = 'Upper Limb Therapy'

       THEN 'Occupational Therapy'

       ELSE [Therapy service] END




    Kent Waldrop
    • Marked as answer by akhlaq768 Monday, June 15, 2009 2:00 PM
    Monday, June 15, 2009 1:36 PM