Counting Y's and N's in record Help!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Counting Y's and N's in record Help!

Author

Topic

AvisRick
Starting Member

3 Posts

Posted - 2007-04-13 : 12:47:07

All,
I have a table that was created for a web form. The fields are populated with Y, N, NC or NA.
I need to total these for each record. There are about 40 fields
in each record.
The expected result
Form Y N NA

1 4 2 34
2 8 2 30
3 40 0 0
Thanks for you Help

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-04-13 : 12:51:26

Can you post expected result?

Madhivanan

Failing to plan is Planning to fail

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-13 : 12:57:42

You can do a count(*) with a GROUP BY on the column. For more specific answer, please post the table structure, some sample data and expected result.

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-13 : 13:18:33

The basic idea is something like

SELECT Form,
CASE WHEN Col1 = 1 THEN 1 ELSE 0 END +
CASE WHEN ColGreen > 5 THEN 1 ELSE 0 END AS [Y],
CASE WHEN Col2 < 4 THEN 1 ELSE 0 END +
CASE WHEN ColBlue = 0 THEN 1 ELSE 0 END AS [N],
CASE WHEN Col3 = 'R' THEN 1 ELSE 0 END +
CASE WHEN ColYellow = 'D' THEN 1 ELSE 0 END AS [NA]
FROM Table1
ORDER BY Form

Peter Larsson
Helsingborg, Sweden

AvisRick
Starting Member

3 Posts

Posted - 2007-04-13 : 14:16:52

quote:
Originally posted by dinakar

You can do a count(*) with a GROUP BY on the column. For more specific answer, please post the table structure, some sample data and expected result.

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Here is a bit of the table structure:
CREATE TABLE [dbo].[QA_Form1_Data](
[QAFormID] [int] IDENTITY(1,1) NOT NULL,
[Observer_ID] [int] NOT NULL,
[Agent_ID] [numeric](18, 0) NOT NULL,
[Location] [varchar](50) NOT NULL,
[Brand] [nchar](10) NULL,
[Supervisor] [nchar](30) NULL,
[TeamNumber] [nchar](10) NULL,
[DeptName] [nchar](30) NULL,
[ReportGroup] [nchar](20) NULL,
[Report_date] [smalldatetime] NOT NULL,
[NOB_Pickup_L_D_T] [char](3) NOT NULL CONSTRAINT [DF_Table_1_NOB_Pick up L_D_T] DEFAULT ('N/A'),
[NOB_Return_L_D_T] [char](3) NOT NULL CONSTRAINT [DF_Table_1_NOB_Return L_D_T] DEFAULT ('N/A'),
[NOB_Car_group_verification] [char](3) NOT NULL CONSTRAINT [DF_QA_Form1_Car_group_verification] DEFAULT ('N/A'),
[NOB_Airline_Flight] [char](3) NOT NULL CONSTRAINT [DF_QA_Form1_Airline_Flight] DEFAULT ('N/A'),
[NOB_Wizard_Corp_Coupons] [char](3) NOT NULL CONSTRAINT [DF_QA_Form1_Wizard_Corp_Coupons] DEFAULT ('N/A'),
[NOB_Cid_Age_Screening] [char](3) NOT NULL CONSTRAINT [DF_QA_Form1_Cid_Age_Screening] DEFAULT ('N/A'),
[NOB_CID_Type_debit] [char](3) NOT NULL CONSTRAINT [DF_QA_Form1_Data_CID_Type_debit] DEFAULT ('N/A'),
[NOB_Apply_Policy_Proc_Tools] [char](3) NOT NULL CONSTRAINT [DF_QA_Form1_Data_NOB_Apply_Policy_Proc_Tools] DEFAULT ('N/A'),
[NOB_Use_Tools_for_Info] [char](3) NOT NULL CONSTRAINT [DF_QA_Form1_Data_Use_Tools_for_Info] DEFAULT ('N/A'),
[NOB_Acurate_RT_QT] [char](3) NOT NULL CONSTRAINT [DF_QA_Form1_Data_Acurate_RT_QT] DEFAULT ('N/A'),
[NOB_Offer_To_Book] [char](3) NOT NULL CONSTRAINT [DF_QA_Form1_Data_Offer_To_Book] DEFAULT ('N/A')

Desired Results

QAFormID Y N NA
28 4 4 6
29 2 3 5
30 6 7 8

Y = total of all Y(s) for all record from all fields
N = total of all Y(s) for all record from all fields
NA = total of all Y(s) for all record from all fields

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-13 : 14:20:58

One more time...

SELECT Form,
SUM(CASE WHEN Col1 = 'Y' THEN 1 ELSE 0 END + 
CASE WHEN Col2 = 'Y' THEN 1 ELSE 0 END) AS [Y],
SUM(CASE WHEN Col1 = 'N' THEN 1 ELSE 0 END + 
CASE WHEN Col2 = 'N' THEN 1 ELSE 0 END) AS [N],
SUM(CASE WHEN Col1 = 'N/A' THEN 1 ELSE 0 END + 
CASE WHEN Col2 = 'N/A' THEN 1 ELSE 0 END) AS [NA]
FROM Table1
GROUP BY Form
ORDER BY Form

Peter Larsson
Helsingborg, Sweden

AvisRick
Starting Member

3 Posts

Posted - 2007-04-13 : 18:08:38

quote:

Originally posted by Peso

One more time...

SELECT Form,
SUM(CASE WHEN Col1 = 'Y' THEN 1 ELSE 0 END + 
CASE WHEN Col2 = 'Y' THEN 1 ELSE 0 END) AS [Y],
SUM(CASE WHEN Col1 = 'N' THEN 1 ELSE 0 END + 
CASE WHEN Col2 = 'N' THEN 1 ELSE 0 END) AS [N],
SUM(CASE WHEN Col1 = 'N/A' THEN 1 ELSE 0 END + 
CASE WHEN Col2 = 'N/A' THEN 1 ELSE 0 END) AS [NA]
FROM Table1
GROUP BY Form
ORDER BY Form

Peter Larsson
Helsingborg, Sweden

This works great! - Thanks for your help.
Rick

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources