COUNT of each value at one column ?

Question

0.00/5 (No votes)

See more:

i have table with 2 column ( name , value ) , value can be ( 0,1,null ),
i want to select (name , count of value = 0 , count of value = 1 , count of value = null ) group by name , how can i do that ??
thanks ..

Posted 11-Feb-11 23:16pm

MohammedSabry

Add a Solution

3 solutions

Solution 3

You want a PIVOT query :

SELECT *
FROM (
SELECT name,
            CASE value 
            WHEN NULL THEN 'no value'
            WHEN 0 THEN 'zero'
            WHEN 1 THEN 'one'
            END AS thevalue
 FROM [yourDB].[youruser].[yourTable]) AS datatable
 PIVOT
( COUNT(thevalue)
  FOR thevalue IN ([no value],[zero],[one])
) AS myquery

Something like that.

Cheers

Posted 12-Feb-11 0:04am

Estys

Updated 12-Feb-11 0:28am

v3

Comments

MohammedSabry 13-Feb-11 19:21pm

it gives me error
{Msg 325, Level 15, State 1, Procedure StoredProcedure7, Line 11
Incorrect syntax near 'PIVOT'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the stored procedure sp_dbcmptlevel.
}

Estys 14-Feb-11 3:16am

Hi Mohammed, what version of SQLSERVER are you on? My example works, I tested it on SQL2008. I'm not sure when PIVOT was introduced.

Solution 4

SELECT        Districts.name, COUNT(DISTINCT Interview_Schedule.app_id) AS [APP] ,                                                                         (COUNT(Interview_Schedule.value)FROM            Districts INNER JOIN
                         Interview_Schedule ON Districts.id = Interview_Schedule.interview_district WHERE Interview_Schedule.value=1)AS [ONES],
(COUNT(Interview_Schedule.value)FROM            Districts INNER JOIN
                         Interview_Schedule ON Districts.id = Interview_Schedule.interview_district WHERE Interview_Schedule.value=0)AS [ZEROS],
(COUNT(Interview_Schedule.value)FROM            Districts INNER JOIN
                         Interview_Schedule ON Districts.id = Interview_Schedule.interview_district WHERE Interview_Schedule.value IS NULL)AS [NULLS]
FROM            Districts INNER JOIN
                         Interview_Schedule ON Districts.id = Interview_Schedule.interview_district
WHERE        (Interview_Schedule.work_flow_id = 2)
GROUP BY Districts.name

Posted 12-Feb-11 4:04am

MohammedSabry

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2011-02-11T23:37:00

Solution 2

AS you already said yourself, you can use GROUP BY clause for that. For example:
SELECT Name, Value, COUNT(*)
FROM ...
GROUP BY Name, Value

Regards,

mika

Posted 11-Feb-11 23:37pm

Wendelius

Comments

MohammedSabry 12-Feb-11 5:46am

i dont want count of all i want my output of column ( value ) = 3 column each column contain count of each value ( count of zeros , count of ones , count of nulls )

Wendelius 12-Feb-11 5:55am

Ok, so if you have data like:
A 0
A 1
A 1

Do you want your output to be like:
Name CountNULL Count0 Count 1
A 0 1 2

MohammedSabry 12-Feb-11 6:09am

the column (value) may contain (0) or (1) or (NULL)
I want the output to be three columns
column 1 --> COUNT (value) AS ZEROS when it`s = 0
column 2 --> COUNT (value) AS ONES when it`s = 1
column 3 --> COUNT (value) AS NULLS when it`s = NULL

got it ??

Wendelius 12-Feb-11 6:14am

Yep, Pivoting is one possibility and the other is to use scalars. For example:
SELECT (COUNT(*) FROM YourTable WHERE Value IS NULL) AS Col1,
(COUNT(*) FROM YourTable WHERE Value = 0) AS Col2
and so on...

MohammedSabry 12-Feb-11 9:20am

it`s not working

Wendelius 12-Feb-11 9:24am

Could you post the actual SQL statement you use and the error messsage. Without those it's hard to say what's the problem.

MohammedSabry 12-Feb-11 10:14am

I did put it please check it and tell me whats wrong ,its give me error
{Error in list of function arguments: 'FROM' not recognized.
Unable to parse query text.}

Wendelius 12-Feb-11 10:37am

Okay,

I made a small query but I'm unable to verify it since I don't have the same tables as you have.

The scalars in the SELECT portion are now correlated to the outer query (Districts) so each scalar will fetch only rows where Interview_Schedule.interview_district is same as Districts.id from the outer portion.

I included only the calculation for rows having value equal to 1 in order to keep the query small. If this works, you can add rest of the scalars to it using the same idea.

The query:
SELECT Districts.name,
( SELECT COUNT(DISTINCT Interview_Schedule.app_id)
FROM Interview_Schedule
WHERE Districts.id = Interview_Schedule.interview_district) AS [APP],
( SELECT COUNT(*)
FROM Interview_Schedule
WHERE Districts.id = Interview_Schedule.interview_district
AND Interview_Schedule.value = 1) AS [ONES]
FROM Disticts
WHERE (Interview_Schedule.work_flow_id = 2)

Let me know if it works out.

MohammedSabry 12-Feb-11 11:02am

The multi-part identeifier "Interview_Schedule.work_flow_id" could not be bound

Wendelius 12-Feb-11 11:11am

Damn, sorry. I accidentally removed the inner join from the outer part. More like:

SELECT Districts.name,
( SELECT COUNT(DISTINCT Interview_Schedule.app_id)
FROM Interview_Schedule
WHERE Districts.id = Interview_Schedule.interview_district) AS [APP],
( SELECT COUNT(*)
FROM Interview_Schedule
WHERE Districts.id = Interview_Schedule.interview_district
AND Interview_Schedule.value = 1) AS [ONES]
FROM Disticts INNER JOIN Interview_Schedule
ON Districts.id = Interview_Schedule.interview_district
WHERE (Interview_Schedule.work_flow_id = 2)

MohammedSabry 12-Feb-11 11:25am

it works thanks

Wendelius 12-Feb-11 11:42am

You're welcome :)