Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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!

share|improve this question
    
What DB engine please? –  gbn May 27 '11 at 12:15
    
Is there a solution for all engines? –  Tie-fighter May 27 '11 at 12:26
1  
You need a numbers table to do it. For SQL Server you can use a CTE to do it dynamically. I'll add an answer assuming a numbers table. –  gbn May 27 '11 at 12:31

1 Answer 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;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.