Help with query

Hello,

I have two queries

SELECT TOP (100) PERCENT Player_name, SUM([Top-ups]) AS TOPUPS
FROM (SELECT dbo.Event_data.Transaction_type, dbo.Players.Player_name, dbo.Events.Top_up, dbo.Event_data.Transaction_value,
dbo.Events.Top_up * dbo.Event_data.Transaction_value AS [Top-ups]
FROM dbo.Event_data INNER JOIN
dbo.Events ON dbo.Event_data.Event_id = dbo.Events.Event_id INNER JOIN
dbo.Players ON dbo.Event_data.Player_id = dbo.Players.Player_id
WHERE (dbo.Event_data.Transaction_type = 2)) AS Topups
GROUP BY Player_name
ORDER BY TOPUPS DESC

and

SELECT TOP (100) PERCENT Player_name, SUM(Expr1) AS Expr1
FROM (SELECT TOP (100) PERCENT dbo.Event_data.Transaction_value, dbo.Players.Player_name, dbo.Events.Rebuys,
dbo.Event_data.Transaction_value * dbo.Events.Rebuys AS Expr1
FROM dbo.Event_data INNER JOIN
dbo.Events ON dbo.Event_data.Event_id = dbo.Events.Event_id INNER JOIN
dbo.Players ON dbo.Event_data.Player_id = dbo.Players.Player_id
WHERE (dbo.Event_data.Transaction_type = 3)
ORDER BY Expr1 DESC) AS REBUYS
GROUP BY Player_name
ORDER BY Expr1 DESC

Can I combine these into one query to get the Player_name result, rebuys and top ups?

Sure, pop a UNION between the queries.

SELECT TOP (100) PERCENT Player_name, SUM([Top-ups]) AS TOPUPS
FROM (SELECT dbo.Event_data.Transaction_type, dbo.Players.Player_name, dbo.Events.Top_up, dbo.Event_data.Transaction_value,
dbo.Events.Top_up * dbo.Event_data.Transaction_value AS [Top-ups]
FROM dbo.Event_data INNER JOIN
dbo.Events ON dbo.Event_data.Event_id = dbo.Events.Event_id INNER JOIN
dbo.Players ON dbo.Event_data.Player_id = dbo.Players.Player_id
WHERE (dbo.Event_data.Transaction_type = 2)) AS Topups
GROUP BY Player_name
ORDER BY TOPUPS DESC

UNION

SELECT TOP (100) PERCENT Player_name, SUM(Expr1) AS Expr1
FROM (SELECT TOP (100) PERCENT dbo.Event_data.Transaction_value, dbo.Players.Player_name, dbo.Events.Rebuys,
dbo.Event_data.Transaction_value * dbo.Events.Rebuys AS Expr1
FROM dbo.Event_data INNER JOIN
dbo.Events ON dbo.Event_data.Event_id = dbo.Events.Event_id INNER JOIN
dbo.Players ON dbo.Event_data.Player_id = dbo.Players.Player_id
WHERE (dbo.Event_data.Transaction_type = 3)
ORDER BY Expr1 DESC) AS REBUYS
GROUP BY Player_name
ORDER BY Expr1 DESC

|||

Thanks for replying, but I am getting an "incorrect syntax near the keyword Union"

am I missing a comma or something?

|||

You should not use ORDER BY for subqueries.

Also if you are selecting all the rows why bother with top 100 PERCENT.

SELECT Player_name,SUM([Top-ups])AS TOPUPSFROM (SELECT dbo.Event_data.Transaction_type, dbo.Players.Player_name, dbo.Events.Top_up, dbo.Event_data.Transaction_value, dbo.Events.Top_up * dbo.Event_data.Transaction_valueAS [Top-ups]FROM dbo.Event_dataINNERJOIN dbo.EventsON dbo.Event_data.Event_id = dbo.Events.Event_idINNERJOIN dbo.PlayersON dbo.Event_data.Player_id = dbo.Players.Player_idWHERE (dbo.Event_data.Transaction_type = 2))AS TopupsGROUP BY Player_name--ORDER BY TOPUPS DESCUNION SELECT Player_name,SUM(Expr1)AS TOPUPSFROM (SELECT dbo.Event_data.Transaction_value, dbo.Players.Player_name, dbo.Events.Rebuys, dbo.Event_data.Transaction_value * dbo.Events.RebuysAS Expr1FROM dbo.Event_dataINNERJOIN dbo.EventsON dbo.Event_data.Event_id = dbo.Events.Event_idINNERJOIN dbo.PlayersON dbo.Event_data.Player_id = dbo.Players.Player_idWHERE (dbo.Event_data.Transaction_type = 3) )AS REBUYSGROUP BY Player_name--ORDER BY TOPUPS DESC

|||

OK Its working but I am only getting two colums Player_name and TOPUPS, when I really want Three coloums Player_name TOPUPS and REBUYS.

I am getting the player name twice in the player column with the rebuy total under the TOPUP column.

Any Ideas?

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

UNION

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

what I am getting

Player_nameTOPUPSJohnSmith100John Smith400John Doe3600John Doe3700

What I want

Player_nameTOPUPSBUYINJohnSmith100400John Doe36003700

Thanks again.

|||

You could then insert the result of each of the queries into a table varible and do a SELECT from it.

declare @.ttable( player_namevarchar(100), topupsint, buyingint)INSERT INTO @.t (player_name, topups, buying )SELECT Player_name,SUM([Top-ups])AS TOPUPS ,NULLFROM (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 EventsON Event_data.Event_id = Events.idINNERJOIN PlayersON Event_data.Player_id = Players.Player_idWHERE Event_data.Transaction_type = 2--AS TopupsGROUP BY Player_name )AS TopupsINSERT INTO @.t (player_name, topups, buying )SELECT Player_name,NULL,SUM([Re-buys])AS REBUYSFROM (SELECT Event_data.Transaction_value, Players.Player_name, Events.Rebuys, Event_data.Transaction_value * Events.RebuysAS [Re-buys]FROM Event_dataINNERJOIN EventsON Event_data.Event_id = Events.idINNERJOIN PlayersON Event_data.Player_id = Players.Player_idWHERE Event_data.Transaction_type = 3--AS REBUYSGROUP BY Player_name )AS REBUYSSELECT player_name,min(topups),min(buying)FROM @.tGROUP BY player_nameORDER BY player_name

|||

Once again thank you for all your help. I am going to mark the previous one as the answer, but I still have one outstanding issue: the return results give me the column name of player_name but the other two say no column name even thought the totals are correct. Heres what I have:

declare

@.ttable( player_namevarchar(100), topups1int, buying1int)

INSERT

INTO @.t(player_name, topups1)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, buying1)

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

SELECT

player_name,min(topups1),min(buying1)

FROM

@.t

GROUP

BY player_name

ORDER

BY player_name|||

Give column names for your expressions in your last SELECT statement.

SELECTplayer_name,min(topups1) AS topups1,min(buying1) AS buying1

FROM@.t

GROUPBY player_name

ORDERBY player_name

|||Excellent!!! Thank you very much