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.
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 fieldsin each record. The expected result Form Y N NA 1 4 2 342 8 2 303 40 0 0Thanks for you Help |
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-04-13 : 12:51:26
|
Can you post expected result?MadhivananFailing 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 likeSELECT 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 Table1ORDER BY FormPeter LarssonHelsingborg, 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 ResultsQAFormID Y N NA28 4 4 629 2 3 530 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 Table1GROUP BY FormORDER BY Form Peter LarssonHelsingborg, 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 Table1GROUP BY FormORDER BY Form Peter LarssonHelsingborg, Sweden
This works great! - Thanks for your help.Rick |
|
|
|
|
|
|
|