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
Go to Top of Page

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/
Go to Top of Page

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
Go to Top of Page

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


Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -