Counting values in columns

Question

What I am looking for is to group by and count the total of different data in the same table and have them show in two different columns. Like below.

Data in table A

Fields: Name - Type

   Bob  - 1  
   John - 2 
   Bob  - 1 
   Steve -1 
   John - 1 
   Bob - 2

Desired result from query:

Name - Type 1 - Type 2

    Bob - 2 - 1
    John - 1 - 1
    Steve - 1 - 0

Answer 1

@Seb has a good solution, but it's server-dependent. Here's an alternate using subselects that should be portable:

select
  name,
  (select count(type) from myTable where type=1 and name=a.name) as type1,
  (select count(type) from myTable where type=2 and name=a.name) as type2
from
  myTable as a
group by 
  name

Answer 2

Use two separate GROUP BY subqueries.

SELECT Name, a.Count1, b.Count2
from myTable
JOIN
  (SELECT Name, SUM(Type) AS Count1 FROM myTable GROUP BY Name WHERE Type=1) AS a ON a.Name = myTable.Name
  (SELECT Name, SUM(Type) FROM myTable GROUP BY Name WHERE Type=2) AS b ON b.Name = myTable.Name

Answer 3

No time to write the code, but the Case statement is what you want here. SImply havea value of 1 if it meets the case and zero if it deosn't. Then you can sum the columns.

Answer 4

This will do the trick in SQL Server:

SELECT
  name,
  SUM( CASE type WHEN 1 THEN 1 ELSE 0 END) AS type1,
  SUM( CASE type WHEN 2 THEN 1 ELSE 0 END) AS type2
FROM
  myTable
GROUP BY
  name

Answer 5

You're looking for a CrossTab solution. The above solutions will work, but you'll come unstuck if you want a general solution and have N types.

A CrossTab solution will solve this for you. If this is for quickly crunching some numbers then dump your data into Excel and use the native Pivot Table feature.

If it's for a RDBMS in an app, then it depends upon the RDBMS. MS SQL 2005 and above has a crosstab syntax. See:

http://www.databasejournal.com/features/mssql/article.php/3521101/Cross-Tab-reports-in-SQL-Server-2005.htm