CODEWITHSUNDEEP
sqlsql-server
Jim Becker
Jim Becker

Reputation: 209

SQL query to Group By and Concatenate

I have a table as so:

Date Opponent Score Result
09/14/56 Shawen 0-18 L
09/21/56 Dixie 12-16 L
... ... ... ...
10/29/60 Carlisle 0-6 L
11/12/60 Shawen 22-0 W

I'm trying to create a query which will produce this result:

Year W-L
1956 1-4
1957 4-3
1958 3-6
1959 3-5
1960 7-1

I am able to run these queries independently:

SELECT YEAR(Date), COUNT(Result) From GameSummaries WHERE Result = 'W' GROUP BY YEAR(Date)
SELECT YEAR(Date), COUNT(Result) From GameSummaries WHERE Result = 'L' GROUP BY YEAR(Date)

Which return:

Year W
1956 1
1957 4
1958 3
1959 3
1960 7

And:

Year L
1956 4
1957 3
1958 6
1959 5
1960 1

I am able to run this query:

SELECT CONCAT(w, '-', l) FROM (SELECT
    (SELECT COUNT(Result) FROM GameSummaries WHERE Result = 'W') AS w,
    (SELECT COUNT(Result) FROM GameSummaries WHERE Result = 'L') AS l) as c

Which returns: 18-19

But I haven't been able to figure out the syntax to put them together.

My best effort is:

SELECT YEAR(Date), CONCAT(w, '-', l) FROM (SELECT
    (SELECT YEAR(Date), COUNT(Result) FROM GameSummaries WHERE Result = 'W' GROUP BY YEAR(Date)) AS w,
    (SELECT YEAR(Date), COUNT(Result) FROM GameSummaries WHERE Result = 'L' GROUP BY YEAR(Date)) AS l) as c

Which returns:

Msg 116, Level 16, State 1, Line 14 Only one expression can be specified in the select list when the subquery is not introduced with EXISTS. Msg 116, Level 16, State 1, Line 15 Only one expression can be specified in the select list when the subquery is not introduced with EXISTS. Msg 207, Level 16, State 1, Line 13 Invalid column name 'Date'.

This seems like it should be fairly basic, can someone please point me in the right direction?

Upvotes: 0

Views: 60

Answers (1)

Hogan
Hogan

Reputation: 70513

You can use SUM with a case statement as a trick

SELECT YEAR(date) as [year],
   sum(CASE WHEN Result = 'W' THEN 1 ELSE 0 END) + '-'
   sum(CASE WHEN Result = 'L' THEN 1 ELSE 0 END) as [W-L]
FROM GameSummaries
GROUP BY Year(Date)

Note you may need to cast to string if you get an error change to this

   cast(sum(CASE WHEN Result = 'W' THEN 1 ELSE 0 END) as varchar(20)) + '-'
   cast(sum(CASE WHEN Result = 'L' THEN 1 ELSE 0 END) as varchar(20)) as [W-L]

Upvotes: 0

Related Questions