views:

570

answers:

4

Let's say I have a database column 'grade' like this:

|grade|
|    1|
|    2|
|    1|
|    3|
|    4|
|    5|

Is there a non-trivial way in SQL to generate a histogram like this?

|2,1,1,1,1,0|

where 2 means the grade 1 occurs twice, the 1s mean grades {2..5} occur once and 0 means grade 6 does not occur at all.

I don't mind if the histogram is one row per count.

If that matters, the database is SQL Server accessed by a perl CGI through unixODBC/FreeTDS.

EDIT: Thanks for your quick replies! It is okay if non-existing values (like grade 6 in the example above) do not occur as long as I can make out which histogram value belongs to which grade.

+4  A: 
SELECT COUNT(grade) FROM table GROUP BY grade ORDER BY grade

Haven't verified it, but it should work.It will not, however, show count for 6s grade, since it's not present in the table at all...

Ilya Volodin
This works (have verified it)
Gamecat
+1  A: 
select Grade, count(Grade)
from MyTable
group by Grade
Terrapin
+1  A: 

Gamecat's use of DISTINCT seems a little odd to me, will have to try it out when I'm back in the office...

The way I would do it is similar though...

SELECT
    [table].grade        AS [grade],
    COUNT(*)             AS [occurances]
FROM
    [table]
GROUP BY
    [table].grade
ORDER BY
    [table].grade

To overcome the lack of data where there are 0 occurances, you can LEFT JOIN on to a table containing all valid grades. The COUNT(*) will count NULLS, but COUNT(grade) won't count the NULLS.

DECLARE @grades TABLE (
   val INT
   )  

INSERT INTO @grades VALUES (1)  
INSERT INTO @grades VALUES (2)  
INSERT INTO @grades VALUES (3)  
INSERT INTO @grades VALUES (4)  
INSERT INTO @grades VALUES (5)  
INSERT INTO @grades VALUES (6)  

SELECT
    [grades].val         AS [grade],
    COUNT([table].grade) AS [occurances]
FROM
    @grades   AS [grades]
LEFT JOIN
    [table]
        ON [table].grade = [grades].val
GROUP BY
    [grades].val
ORDER BY
    [grades].val
Dems
Yes, I was wrong. And missed the group by. SQL to rusty error.
Gamecat
A: 

Use a temp table to get your missing values:

CREATE TABLE #tmp(num int)
DECLARE @num int
SET @num = 0
WHILE @num < 10
BEGIN
  INSERT #tmp @num
  SET @num = @num + 1
END


SELECT t.num as [Grade], count(g.Grade) FROM gradeTable g
RIGHT JOIN #tmp t on g.Grade = t.num
GROUP by t.num
ORDER BY 1
ck