Click here to Skip to main content
15,064,072 members
Please Sign up or sign in to vote.
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

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
   
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 :)
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
   
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.
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
   

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900