Counting column values in SQL

Question

0.00/5 (No votes)

See more:

I have a problem,I have a table
ImpID 001
Number01 X
Number02 X
NumBer03 X
Number04 X
........
Number31 X

I were Load Report:

EmpID	01	02	03	04	....	31	SUM
001	X	X	X	X	....	X	31

How I can query by SQL for count char 'X'?I plan to convert rows into columns, but I query fails. Please help me. Thanks all

Posted 7-Jun-12 7:11am

violetqa

Add a Solution

Comments

Maciej Los 7-Jun-12 13:19pm

Not clear! Please, provide more details. What exactly is the structure of table (example data with column-headers)? Please, elaborate it. Use "Improve question" button.
What have you done till now? Show us your query...

Corporal Agarn 7-Jun-12 13:41pm

Does your table have only two columns? Does the EmpID changing signify that the count is to start again? Can there be missed NumberXX say no Number04 but a Number05?
As Iosmac said please show what you have done.

2 solutions

Solution 1

I think you should not change rows to columns...What I estimate from your question is:

You have (should have)
a.) a table EMP with fields
EMP.EMPID
EMP.ANYTHING_OTHER
EMP.THIS_AN_THAT

b.) a table EMP_DTL with fields
EMP_DTL.EMPID
EMP_DTL.EMP_DTL_POS
EMP_DTL.NUMBER

If this assumption is right you can easily solve your request.

SQL

SELECT 
	COUNT(*)
FROM EMP_DTL
WHERE EMP_DTL.EMPID = '001' AND EMP_DTL.NUMBER = 'X'

or better with parameters to count the "X" or the "Y" (query parameter :NUMBER) etc. for any Imp (parameter :EMPID)

XML

SELECT
    COUNT(*)
FROM EMP_DTL
WHERE EMP_DTL.EMPID = :EMPID AND EMP_DTL.NUMBER = :NUMBER

And like this you much more possibilities using GROUP BY, HAVING...

Hope I estimated right. Regards.

Posted 7-Jun-12 7:49am

idle63

Comments

Manas Bhardwaj 7-Jun-12 14:46pm

Good answer 5!

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)

Maciej Los · Accepted Answer · 2012-06-07T08:43:00

Let's say, you have a table name MyData like this: EmpID VARCHAR(3), Number VARCHAR(3), IsSelected VARCHAR(3)
It stores the values:

EmpId	Number	IsSelected
001	01	x
001	02	x
001	03	x
001	04	x
001	05	x
...
001	31	x

SQL

DECLARE @sqry NVARCHAR(2000)
DECLARE @dtqry NVARCHAR(2000)
DECLARE @pvqry NVARCHAR(2000)
DECLARE @cols NVARCHAR(2000)

--select all Numbers to use it as dynamic columns
SET @cols = STUFF(( SELECT DISTINCT '],[' + [Number]
		FROM [dbo].[Mydata]
		ORDER BY '],[' + [Number]
		FOR XML PATH('')), 1, 2, '') + ']'

--count numbers for each empid
SET @dtqry = 'SELECT [EmpId], [Number] , COUNT([IsSelected]) AS [CIS] ' +
		'FROM [dbo].[MyData] ' +
		'GROUP BY [EmpId]'
EXECUTE (@dtqry)

--pivot empid for number
SET @ptqry = 'SELECT [EmpId], ' + @cols + ' ' +
			'FROM (' + @dtqry + ') AS DT ' + 
			'PIVOT (SUM(DT.[CIS]) FOR DT.[Number] IN (' + @cols + ')) AS PT ' + 
			'ORDER BY PT.[EmpId]'
EXECUTE (@ptqry)

Resultset:

EmpId	01	02	03	04	05	...	31
001	1	1	1	1	1	...	1

More about: Using Pivot and Upivot[^]

How to get total? See my answers in similar discussions (examples):
Find 3 item association rules in MS SQL[^]
rows to columns change in sql[^]
Problem in calculation[^]

Counting column values ​​in SQL