Results 1 to 10 of 10

Thread: Derived Query

  1. #1
    Join Date
    Feb 2012
    Posts
    188

    Unanswered: Derived Query

    I am executing the below query so I can get a group by performed on the entire returned result set. Problem is, the result set being returned is deflated! Did I set this query up incorrectly?

    Code:
    Select itemsold,
     Count(*)
    From 
    (Select itemsold,
     Count(*)
    From table1
    Where salespersonstatus = 'active'
    Group by itemsold
    Union all
    Select itemsold,
    Count(*)
    From table2
    Where salespersonstatus = 'active'
    Group by itemsold)
    Derived Group By itemsold

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I think you want to SUM the counts of the subquery, in order to get the total sold per itemsold.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try this:
    Code:
    Select itemsold, SUM(nmbr) as nmbr
    From 
    	(Select itemsold, Count(*) as nmbr
    	From table1
    	Where salespersonstatus = 'active'
    	Group by itemsold
    		Union all
    	Select itemsold, Count(*) as nmbr
    	From table2
    	Where salespersonstatus = 'active'
    	Group by itemsold
    	)
    Group By itemsold
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Hey Blindman,
    Sorry. Seems our replies have crossed.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Feb 2012
    Posts
    188
    Quote Originally Posted by Wim View Post
    Try this:
    Code:
    Select itemsold, SUM(nmbr) as nmbr
    From 
    	(Select itemsold, Count(*) as nmbr
    	From table1
    	Where salespersonstatus = 'active'
    	Group by itemsold
    		Union all
    	Select itemsold, Count(*) as nmbr
    	From table2
    	Where salespersonstatus = 'active'
    	Group by itemsold
    	)
    Group By itemsold

    That got it, thanks so much!

  6. #6
    Join Date
    Feb 2012
    Posts
    188
    What if I need to throw a case in their? I keep getting an invalid column name when I try
    Code:
    Select itemsold as [sold item], sum(nbr) As nbr
    From (select case itemsold when 'blanket' then 'blank'
    When 'sheet' then 'shot'
    Else itemsold
     End as [sold item],
    Count(*) as nbr
    From table
    Group by
    Case items old
    When 'blanket' then 'blank'
    When 'sheet' then 'shot'
    Else itemsold
     End
    .........

  7. #7
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    It's all in the details.
    Code:
    Select itemsold as [sold item], sum(nbr) As nbr
    From (select case itemsold when 'blanket' then 'blank'
    		When 'sheet' then 'shot'
    		Else itemsold
    	End as itemsold,
    	Count(*) as nbr
    	From table
    	Group by Case items old
    		When 'blanket' then 'blank'
    		When 'sheet' then 'shot'
    		Else itemsold
    	End
    Both names must be equal.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  8. #8
    Join Date
    Feb 2012
    Posts
    188
    Quote Originally Posted by Wim View Post
    It's all in the details.
    Code:
    Select itemsold as [sold item], sum(nbr) As nbr
    From (select case itemsold when 'blanket' then 'blank'
    		When 'sheet' then 'shot'
    		Else itemsold
    	End as itemsold,
    	Count(*) as nbr
    	From table
    	Group by Case items old
    		When 'blanket' then 'blank'
    		When 'sheet' then 'shot'
    		Else itemsold
    	End
    Both names must be equal.
    Learned something new this day. I always thought the "as" names had to match. Thanks so much!!!

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by Wim View Post
    Try this:
    Code:
    Select itemsold, SUM(nmbr) as nmbr
    From 
    	(Select itemsold, Count(*) as nmbr
    	From table1
    	Where salespersonstatus = 'active'
    	Group by itemsold
    		Union all
    	Select itemsold, Count(*) as nmbr
    	From table2
    	Where salespersonstatus = 'active'
    	Group by itemsold
    	)
    Group By itemsold
    This might be antoher (a little shorter) way...
    Code:
    Select itemsold
         , Count(*) AS nmbr
     From  (Select itemsold
             From  table1
             Where salespersonstatus = 'active'
            Union all
            Select itemsold
             From  table2
             Where salespersonstatus = 'active'
           ) Derived
     Group By
           itemsold

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by Wim View Post
    It's all in the details.
    Code:
    Select itemsold as [sold item], sum(nbr) As nbr
    From (select case itemsold when 'blanket' then 'blank'
    		When 'sheet' then 'shot'
    		Else itemsold
    	End as itemsold,
    	Count(*) as nbr
    	From table
    	Group by Case items old
    		When 'blanket' then 'blank'
    		When 'sheet' then 'shot'
    		Else itemsold
    	End
    Both names must be equal.
    This might be a little shorter version.
    (but, I don't know about execution performance...)
    Code:
    SELECT itemsold AS [sold item]
         , COUNT(*) AS nbr
     FROM  (SELECT CASE itemsold
                   WHEN 'blanket' THEN 'blank'
                   WHEN 'sheet'   THEN 'shot'
                   ELSE itemsold
                   END  AS itemsold
             FROM  table
           ) AS s
     GROUP BY
           itemsold
    ;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •