Problem with sql select query
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 wa开发者_如何学编程y 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 :-)
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
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.
精彩评论