Home / server / Questions / 274470
In Process
Tie-fighter
Asked: 2011-05-28 04:07:50 +0800 CST

Grouping: Returning 0 for groups with no data

  • 772

I have a table with a text field. I constructed a query that takes the datalength of those fields and basically returns a histogram.

SELECT TEXTFieldSize, COUNT(TEXTFieldSize) AS Count FROM (SELECT DATALENGTH(data) AS TEXTFieldSize FROM table) AS Fields GROUP BY TEXTFieldSize ORDER BY TEXTFieldSize;
 ________________
| Length | Count |
|      1 |     5 |
|      3 |     2 |
|      6 |    12 |
|________|_______|

My problem is, that in order to analyse this properly I also need rows for the values that have a count of zero.

SELECT ???
 ________________
| Length | Count |
|      1 |     5 |
|      2 |     0 |
|      3 |     2 |
|      4 |     0 |
|      5 |     0 |
|      6 |    12 |
|________|_______|

Thanks in advance!

sql

1 Answers

  1. Generate a numbers table (SQL Server way)

    CREATE TABLE dbo.Numbers (Num int NOT NULL PRIMARY KEY)
INSERT dbo.Numbers (Num)
SELECT TOP 8000 ROW_NUMBER() OVER (ORDER BY C1.column_id)
FROM sys.columns C1 CROSS JOIN sys.columns C2

    The answer:

    SELECT N.Num AS TEXTFieldSize, COUNT(t.data) AS CountOf
FROM
  dbo.Numbers N
  LEFT JOIN
  mytable t ON N.Num = DATALENGTH(t.data)
WHERE
  N.Num <= (SELECT MAX(DATALENGTH(data)) FROM mytable)
GROUP BY N.Num
ORDER BY N.Num;
    • 1