CODEWITHSUNDEEP
sqlsql-server
user5404433
user5404433

Reputation:

How to display two results within one query?

I have a query below that is able to calculate the total weighting of each team based on the 11 players it choose from:

SELECT TeamID, SUM(PlayerWeighting) as TeamWeight
    FROM (
        SELECT * FROM(
            SELECT pl.*,
                   ROW_NUMBER() OVER(PARTITION BY pl.TeamID, pl.Position ORDER BY NEWID()) AS Rnk
            FROM(
                SELECT DISTINCT p.PlayerID, p.Position, p.PlayerWeighting, p.FirstName, p.Surname, t.TeamID, t.TeamAbbreviation, f.WeekNumber
                FROM dbo.Fixture f
                INNER JOIN dbo.League l ON f.LeagueID = l.LeagueID
                INNER JOIN dbo.Team t ON l.LeagueID = t.LeagueID
                INNER JOIN dbo.Player p ON t.TeamID = p.TeamID
                WHERE f.WeekNumber = 1) 
                pl) po
        WHERE (po.position = 'GK' and po.rnk = 1) OR 
              (po.position = 'DF' and po.rnk <= 4) OR
              (po.position = 'MF' and po.rnk <= 4) OR
              (po.position = 'FW' and po.rnk <= 2) ) as T
    GROUP BY T.TeamID

The second SELECT statement within the above query actually outputs the list of players per team (this was tested before I placed in the first SELECT statement to calculate the total).

What I want to do is somehow display two results. One results showing the team weighting and another results showing the players involved in each team weighting. How do I get the above query to show both results in one execution?

Thank you

UPDATE:

Below is what the query looks like if I execute the whole code:

enter image description here

Below is the player weighting results when I comment out the first SELECT statement in the query:

enter image description here

I just want both tables to appear separately when they are executed. Obviously I can't just copy the query and paste it below and remove the first select statement because the players in the second query could be different to the team weighting generated in the first query if that makes sense

Upvotes: 1

Views: 98

Answers (2)

sallushan
sallushan

Reputation: 1147

While @sagi's answer is good, you have 1 result set with all the things you need. But still if you insist for 2 results you can use a table variable, for example like this,

DECLARE @testTable table(column1 int, column2 varchar(100));

INSERT INTO @testTable(column1, column2)
    SELECT  1, 'abc';           -- <- you can put your query here


-- first result set
SELECT  *
FROM    @testTable;         

-- second result set
SELECT  COUNT(1) AS TotalRows
FROM    @testTable;

Upvotes: 0

sagi
sagi

Reputation: 40481

You should just use SUM() OVER() without the outer query :

    SELECT po.*,
           SUM(po.playerWeighting) OVER(PARTITION BY po.teamID) as teamWeight
    FROM(
        SELECT pl.*,
               ROW_NUMBER() OVER(PARTITION BY pl.TeamID, pl.Position ORDER BY NEWID()) AS Rnk
        FROM(
            SELECT DISTINCT p.PlayerID, p.Position, p.PlayerWeighting, p.FirstName, p.Surname, t.TeamID, t.TeamAbbreviation, f.WeekNumber
            FROM dbo.Fixture f
            INNER JOIN dbo.League l ON f.LeagueID = l.LeagueID
            INNER JOIN dbo.Team t ON l.LeagueID = t.LeagueID
            INNER JOIN dbo.Player p ON t.TeamID = p.TeamID
            WHERE f.WeekNumber = 1) 
            pl) po
    WHERE (po.position = 'GK' and po.rnk = 1) OR 
          (po.position = 'DF' and po.rnk <= 4) OR
          (po.position = 'MF' and po.rnk <= 4) OR
          (po.position = 'FW' and po.rnk <= 2)

SUM() OVER() is an analytic function mostly used for cumulative sum. When no ORDER BY mentioned after the PARTITION BY , this will simply return the total sum for each group mentioned in the PARTITION BY part.

Upvotes: 2

Related Questions