Help with query nulls and addition

Hi I have a query, what I would like to do is create a column that takes the results in two coulms and add them together:

Col A Col B Col C

Row1 1 1 2

Row2 2 3 5

Here is the query

declare

@.ttable( player_namevarchar(100), BuyInint, TopUpint, ReBuyint, Winningsint, Eventsint, Testint)

INSERT

INTO @.t(player_name, TopUp)SELECT Player_name,SUM([Top-ups])AS TOPUPS

FROM

(SELECT Event_data.Transaction_type, Players.Player_name, Events.Top_up, Event_data.Transaction_value,

Events

.Top_up* Event_data.Transaction_valueAS [Top-ups]FROM Event_dataINNERJOIN

Events

ON Event_data.Event_id= Events.idINNERJOIN

Players

ON Event_data.Player_id= Players.Player_idWHERE(Event_data.Transaction_type= 2))AS Topups

GROUP

BY player_name

INSERT

INTO @.t(player_name, ReBuy)

SELECT

Player_name,SUM([Re-buys])AS REBUYS

FROM

(SELECT Event_data.Transaction_value, Players.Player_name, Events.Rebuys, Event_data.Transaction_value* Events.RebuysAS [Re-buys]FROM Event_dataINNERJOIN

Events

ON Event_data.Event_id= Events.idINNERJOIN

Players

ON Event_data.Player_id= Players.Player_idWHERE(Event_data.Transaction_type= 3))AS REBUYS

GROUP

BY Player_name

Insert

into @.t(player_name, BuyIn)

SELECT

dbo.Players.Player_name,SUM(dbo.Events.Buy_in)AS BuyIn

FROM

dbo.PlayersINNERJOIN

dbo

.Event_dataON dbo.Players.Player_id= dbo.Event_data.Player_idINNERJOIN

dbo

.EventsON dbo.Event_data.Event_id= dbo.Events.id

GROUP

BY dbo.Players.Player_name, dbo.Event_data.Transaction_type

HAVING

(dbo.Event_data.Transaction_type= 1)

ORDER

BYSUM(dbo.Events.Buy_in)DESC

Insert

into @.t(player_name, Winnings)

SELECT

dbo.Players.Player_name,SUM(dbo.Event_data.Transaction_value)AS Winnings

FROM

dbo.PlayersINNERJOIN

dbo

.Event_dataON dbo.Players.Player_id= dbo.Event_data.Player_id

GROUP

BY dbo.Players.Player_name, dbo.Event_data.Transaction_type

HAVING

(dbo.Event_data.Transaction_type= 1)

insert

into @.t(player_name, Events)

SELECT

dbo.Players.Player_name,COUNT(dbo.Event_data.Place)AS Expr1

FROM

dbo.PlayersINNERJOIN

dbo

.Event_dataON dbo.Players.Player_id= dbo.Event_data.Player_idINNERJOIN

dbo

.EventsON dbo.Event_data.Event_id= dbo.Events.id

GROUP

BY dbo.Players.Player_name

HAVING

(NOT(COUNT(dbo.Event_data.Place)ISNULL))

insert

into @.t(player_name, test)

select

player_name,((TopUp)+(Rebuy))as Test

from

@.t

SELECT

player_name,min(BuyIn)as BuyIn,min(TopUp)as TopUps,min(ReBuy)as ReBuy,min(Winnings)as Winnings,min(Events)as Events,min(test)as test

FROM

@.t

GROUP

BY player_name

ORDER

BY BuyInDESC

--ORDER BY TOPUPS DESC

END

THis is where I attempt to add the coloms but I get a null result

insertinto @.t(player_name, test)

select

player_name,((TopUp)+(Rebuy))as Test

from

@.t

any help would be great.

You could us the ISNULL fucntion:

insertinto @.t(player_name, test)

select

player_name,(ISNULL(TopUp, 0)+ ISNULL(Rebuy, 0))as Test

from

@.t|||

Hi the is null removes the nulls but I am still unable to add the coloums together, instead of null I get 0 in the test col. I am able to add topup +topup or Buyin + Buyin and I get the result but when I try to add the different cols its null or 0 if I use your suggestion.

any idea?

|||How about using COALESCE instead of ISNULL?|||

Hi. I hav two ideas about that.

1. Declare the column 'test' as a calculated column:

declare @.table TABLE
(player_name varchar(100), BuyIn int, TopUp int, ReBuy int, Winnings int, Events int, Test AS (TopUp + ReBuy))

2. If you, for example SELECT the table for one player use this: "SELECT * FROM table WHERE player_name = 'player1'" and get somethiong like this:

player_name TopUp ReBuy

player1 5 NULL

player1 NULL 3

So, if you sum each row its equal to TopUp + NULL and ReBUy + NULL.

May be you need in the final select this:

SELECT (TopUP + ReBuy) FROM

(SELECT SUM(TopUP) as TopUp, SUM(ReBuy) as ReBuy FROM @.t) As t

or the other option could be to insert the first time, an after that update the rows for the player.

|||

This is most excellent, thanking you exactly what I was looking for: here is my working query with your suggestion. Thanks again a great help!!!

declare

@.ttable( player_namevarchar(100), BuyInint, TopUpint, ReBuyint, Winningsint, Eventsint)

INSERT

INTO @.t(player_name, TopUp)SELECT Player_name,SUM([Top-ups])AS TOPUPS

FROM

(SELECT Event_data.Transaction_type, Players.Player_name, Events.Top_up, Event_data.Transaction_value,

Events

.Top_up* Event_data.Transaction_valueAS [Top-ups]FROM Event_dataINNERJOIN

Events

ON Event_data.Event_id= Events.idINNERJOIN

Players

ON Event_data.Player_id= Players.Player_idWHERE(Event_data.Transaction_type= 2))AS Topups

GROUP

BY player_name

INSERT

INTO @.t(player_name, ReBuy)

SELECT

Player_name,SUM([Re-buys])AS REBUYS

FROM

(SELECT Event_data.Transaction_value, Players.Player_name, Events.Rebuys, Event_data.Transaction_value* Events.RebuysAS [Re-buys]FROM Event_dataINNERJOIN

Events

ON Event_data.Event_id= Events.idINNERJOIN

Players

ON Event_data.Player_id= Players.Player_idWHERE(Event_data.Transaction_type= 3))AS REBUYS

GROUP

BY Player_name

Insert

into @.t(player_name, BuyIn)

SELECT

dbo.Players.Player_name,SUM(dbo.Events.Buy_in)AS BuyIn

FROM

dbo.PlayersINNERJOIN

dbo

.Event_dataON dbo.Players.Player_id= dbo.Event_data.Player_idINNERJOIN

dbo

.EventsON dbo.Event_data.Event_id= dbo.Events.id

GROUP

BY dbo.Players.Player_name, dbo.Event_data.Transaction_type

HAVING

(dbo.Event_data.Transaction_type= 1)

ORDER

BYSUM(dbo.Events.Buy_in)DESC

Insert

into @.t(player_name, Winnings)

SELECT

dbo.Players.Player_name,SUM(dbo.Event_data.Transaction_value)AS Winnings

FROM

dbo.PlayersINNERJOIN

dbo

.Event_dataON dbo.Players.Player_id= dbo.Event_data.Player_id

GROUP

BY dbo.Players.Player_name, dbo.Event_data.Transaction_type

HAVING

(dbo.Event_data.Transaction_type= 1)

insert

into @.t(player_name, Events)

SELECT

dbo.Players.Player_name,COUNT(dbo.Event_data.Place)AS Expr1

FROM

dbo.PlayersINNERJOIN

dbo

.Event_dataON dbo.Players.Player_id= dbo.Event_data.Player_idINNERJOIN

dbo

.EventsON dbo.Event_data.Event_id= dbo.Events.id

GROUP

BY dbo.Players.Player_name

HAVING

(NOT(COUNT(dbo.Event_data.Place)ISNULL))

--insert into @.t (player_name, test)

--select

--player_name, (ISNULL(TopUp, 0) + ISNULL(Rebuy, 0)) as Test

--from @.t

Select

*,(TopUps+ ReBuy+ BuyIn)as Cost,(winnings-(TopUps+ ReBuy+ BuyIn))as Profit

FROM

(SELECT player_name,(ISNULL(min(BuyIn),0))as BuyIn,(ISNULL(min(TopUp),0))as TopUps,(ISNULL(min(ReBuy),0))as ReBuy,min(Winnings)as Winnings,min(Events)as Events

FROM

@.tGROUPBY player_name)as t

GROUP

BY player_name, buyin,topUps, Rebuy, winnings, events

Order

by Profitdesc