SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Counting Y's and N's in record Help!
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AvisRick
Starting Member

3 Posts

Posted - 04/13/2007 :  12:47:07  Show Profile  Reply with Quote
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

Edited by - AvisRick on 04/13/2007 12:57:32

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 04/13/2007 :  12:51:26  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Can you post expected result?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

dinakar
Flowing Fount of Yak Knowledge

USA
2507 Posts

Posted - 04/13/2007 :  12:57:42  Show Profile  Visit dinakar's Homepage  Reply with Quote
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

Sweden
30113 Posts

Posted - 04/13/2007 :  13:18:33  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Edited by - SwePeso on 04/13/2007 14:23:42
Go to Top of Page

AvisRick
Starting Member

3 Posts

Posted - 04/13/2007 :  14:16:52  Show Profile  Reply with Quote
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

Sweden
30113 Posts

Posted - 04/13/2007 :  14:20:58  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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 - 04/13/2007 :  18:08:38  Show Profile  Reply with Quote
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

Edited by - AvisRick on 04/13/2007 18:10:56
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000