Problem with sql select query

Question

I'm having a little problem with [PortfelID] column. I need it's ID to be able to use it in function which will return me name about Type of Strategy per client. However by doing this i need to put [PortfelID] in GroupBy which complicates the results a lot.

I'm looking for a way to find Type of Strategy and Sum of Money this strategy has. However if i use Group By [PortfelID] I'm getting multiple entries per each strategy. Actually over 700 rows (because there are 700 [PortfelID] values). And all I want is just 1 strategy and Sum of [WycenaWartosc] for this strategy. So in total i would get 15 rows or so

Is there a way to use that function without having to add [PortfelID] in Group By?

DECLARE @data DateTime
SET @data = '20100930'

SELECT [dbo].[ufn_TypStrategiiDlaPortfelaDlaDaty] ([PortfelID], @data)
      ,SUM([WycenaWartosc]) AS 'Wycena'   
  FROM[dbo].[Wycena]
  LEFT JOIN [KlienciPortfeleKonta]
  ON [Wycena].[KlienciPortfeleKontaID] = [KlienciPortfeleKonta].[KlienciPortfeleKontaID] 
  WHERE [WycenaData] = @data 
  GROUP BY [PortfelID]

Where [dbo].[ufn_TypStrategiiDlaPortfelaDlaDaty] is defined like this:

ALTER FUNCTION [dbo].[ufn_TypStrategiiDlaPortfelaDlaDaty]
    (
      @portfelID INT,
      @data DATETIME 
    )
RETURNS NVARCHAR(MAX)
AS BEGIN
    RETURN ( SELECT TOP 1
                    [TypyStrategiiNazwa]
             FROM   [dbo].[KlienciPortfeleUmowy]
                    INNER JOIN [dbo].[TypyStrategii]
                    ON dbo.KlienciPortfeleUmowy.TypyStrategiiID = dbo.TypyStrategii.TypyStrategiiID
             WHERE  [PortfelID] = @portfelID
                    AND ( [KlienciUmowyDataPoczatkowa] <= @data
                          AND ([KlienciUmowyDataKoncowa] >= @data
                          OR KlienciUmowyDataKoncowa IS NULL)
                        )
             ORDER BY [KlienciUmowyID] ASC
           )

   end

EDIT:

As per suggestion (Roopesh Majeti) I've made something like this:

  SELECT SUM(CASE WHEN [dbo].[ufn_TypStrategiiDlaPortfelaDlaDaty] ([PortfelID], @data) = 'portfel energetyka' THEN [WycenaWartosc] ELSE 0 END) AS 'Strategy 1'
 ,SUM(CASE WHEN [dbo].[ufn_TypStrategiiDlaPortfelaDlaDaty] ([PortfelID], @data) = 'banków niepublicznych' THEN [WycenaWartosc] ELSE 0 END) AS 'Strategy 2'
  FROM [dbo].[Wycena]
  LEFT JOIN [KlienciPortfeleKonta]
  ON [Wycena].[KlienciPortfeleKontaID] = [KlienciPortfeleKonta].[KlienciPortfeleKontaID] 
  WHERE [WycenaData] = @data

But this seems like a bit overkill and a bit too much of hand job is required. AlexS solution seems to do exactly what I need :-)

AlexS · Accepted Answer · 2010-11-07 18:33:30Z

1

Here's an idea of how you can do this.

DECLARE @data DateTime
SET @data = '20100930'

SELECT 
   TypID,
   SUM([WycenaWartosc]) AS 'Wycena'
FROM 
(
  SELECT [dbo].[ufn_TypStrategiiDlaPortfelaDlaDaty] ([PortfelID], @data) as TypID
  ,[WycenaWartosc]
  FROM[dbo].[Wycena]
  LEFT JOIN [KlienciPortfeleKonta]
  ON [Wycena].[KlienciPortfeleKontaID] = [KlienciPortfeleKonta].[KlienciPortfeleKontaID] 
  WHERE [WycenaData] = @data
) as Q
GROUP BY [TypID]

So basically there's no need to group by PortfelID (as soon as you need to group by output of [dbo].[ufn_TypStrategiiDlaPortfelaDlaDaty]).

This query is not optimal, though. Join can be pushed to the outer query in case PortfelID and WycenaData are not in [KlienciPortfeleKonta] table.

UPDATE: fixed select list and aggregation function application

edited Nov 7, 2010 at 18:33

answered Nov 7, 2010 at 18:07

AlexS

2,38615 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

MadBoy Over a year ago

If i leave [PortfelID] without GROUP BY ->

Msg 8120, Level 16, State 1, Line 16 Column 'KlienciPortfeleKonta.PortfelID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

MadBoy Over a year ago

IF i add inside GROUP BY [PortfelID]) as Q I get

Column 'Q.Wycena' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

AlexS Over a year ago

Sorry for that - looks like I've forgot to fix select list/aggregation. Updated my answer. HTH.

Roopesh Majeti · Accepted Answer · 2010-11-07 17:44:49Z

0

How about using the "Case" statement in sql ? Check the below link for example : http://www.1keydata.com/sql/sql-case.html

Hope this helps.

answered Nov 7, 2010 at 17:44

Roopesh Majeti

5541 gold badge11 silver badges23 bronze badges

11 Comments

MadBoy Over a year ago

I know about CASE STATEMENT just don't see how i would use it here? Or am I missing something ?

Roopesh Majeti Over a year ago

Check this weblink for more suitable syntax example : weblogs.asp.net/wilczynski/archive/2008/06/03/…

Roopesh Majeti Over a year ago

Just to add.. You can use your profile_ID under each "case" and summation into another field. Hope this helps. If not, let me know.. can try to help you more.

MadBoy Over a year ago

I see where you are going. But is there a way to create CASE statement dynamically? The list of strategies is about 15 entries now but it can be 16 in a month or even more later on, and users are given option to add it as they please.

Roopesh Majeti Over a year ago

I think then its going to be tricky. But if you are going to have the above required format, then you might need to go with static case statements, than dynamic. However, I feel that, the change is only addition of 1 more case statement, whenever you have one more category. Is the number of case statements going to increase always or rarely ?

|

Collectives™ on Stack Overflow

Problem with sql select query

2 Answers 2

3 Comments

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related