CODEWITHSUNDEEP
sqlsql-server
Gopal
Gopal

Reputation: 11982

How to take column value count

Using SQL Server 2000

Table1

Column1
  20
  30
  40
  20
  40
  30
  30

I want take a count like this

20 - 2
30 - 3
40 - 2

In case if the column value 20 or 30 or 40 is not available, it should display 20 - 0 or 30 - 0 or 40 - 0.

For example

Column1
20
30
20
30
30

Expected output

20 - 2 
30 - 3
40 - 0

I will get only 20, 30. 40. No more value will come.

How to make a query

Need help

Upvotes: 0

Views: 170

Answers (2)

marc_s
marc_s

Reputation: 754488

SQL Query 101:

SELECT Column1, COUNT(*)
FROM dbo.YourTable
GROUP BY Column1
ORDER BY Column1

Update: if you want to get a list of possible values, and their potential count (or 0) in another table, you need two tables, basically - one with all the possible values, one with the actual values - and a LEFT OUTER JOIN to put them together - something like:

SELECT
    p.Column1, ISNULL(COUNT(t.Column1), 0)
FROM    
    (SELECT 20 AS 'Column1'
     UNION 
     SELECT 30
     UNION 
     SELECT 40) AS p
LEFT OUTER JOIN
    dbo.YourTable t ON t.Column1 = p.Column1
GROUP BY    
    p.Column1
ORDER BY 
    p.Column1

Upvotes: 4

Royi Namir
Royi Namir

Reputation: 148524

select  item,count (item)  from table group by item

EDIT : ( after your edit)

 CREATE TABLE #table1       (          numbers int      ) 
insert into #table1 (numbers) select 20
insert into #table1 (numbers) select 30
insert into #table1 (numbers) select 40


SELECT [num]
  FROM [DavidCard].[dbo].[sssssss]

    select  numbers,count (num)  from #table1  LEFT JOIN [sssssss] ON #table1.numbers =  [sssssss].num      group by numbers

Upvotes: 5

Related Questions