CODEWITHSUNDEEP
sqlsql-serverdatabaset-sqlrelational-database
ccampo
ccampo

Reputation: 1503

Grouping and aggregating field in single SQL query

I have a table laid out as follows:

Table Name: TRANSACTIONS

transactionId | accountId | technology
--------------------------------------
1             | 1         | mac
2             | 1         | linux
3             | 2         | pc
4             | 3         | linux
5             | 2         | pc
6             | 3         | linux
...

I'd like to generate a report with the following format

accountId | num. pc | num. mac | num. linux
-------------------------------------------
1         | 1       | 1        | 0         
2         | 1       | 0        | 1
3         | 0       | 0        | 2
...

I'm not a SQL expert, but I feel I should be able to use a GROUP BY clause to generate these results. Would it be possible to generate this result set in a single query? I am using MS SQL Server 2008 at the moment, so T-SQL works.

Thanks!!

Upvotes: 2

Views: 47

Answers (1)

EricZ
EricZ

Reputation: 6205

You may try something like this

SELECT
   accountId ,
   [num. pc] = SUM(CASE WHEN  technology = 'pc' THEN 1 ELSE 0 END),
   [num. mac] = SUM(CASE WHEN  technology = 'mac' THEN 1 ELSE 0 END),
   [num. linux] = SUM(CASE WHEN  technology = 'linux' THEN 1 ELSE 0 END)
FROM YourTable
GROUP BY accountId

Upvotes: 2

Related Questions