count - SQL Server Forums

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

count

Author

Topic

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-05-03 : 11:03:45

I have a table where records are identified by an Arrestno. There can be multiple records in that table with the same arrestno. there is another column (FSN). Each arrestno could have a different FSN, but there can also be multiple instances of the same FSN for each arrestno. There is a third column (counts) which needs to indicate the like number of FSN entries for each arrrestno. For instance, if Arrestno GCSOARR001038 has three entries, and two of those entries have FSN values of 948.06, then the Counts value for the rows with arrestno GCSOARR001038 and FSN 948.06 will be "2" for Counts. The third FSN value for that arrestno is 901.31. That Counts value for arrestno GCSOARR001038 and FSN 901.31 will be "1". How can i script the value to be set for COUNTS according to this formula?

the ddl for that table is as follows:


CREATE TABLE [dbo].[ARR_AFSS](
	[BEGIN] [varchar](10) NULL,
	[ARRESTNO] [varchar](15) NULL,
	[ARRESTNOB] [varchar](15) NULL,
	[CHARGENUM] [int] NULL,
	[GOC] [varchar](1) NULL,
	[ACL] [varchar](1) NULL,
	[ACD] [varchar](1) NULL,
	[AON] [varchar](4) NULL,
	[FSN] [varchar](30) NULL,
	[COUNTS] [smallint] NULL,
	[FCICCODEV] [varchar](40) NULL,
	[FSDESC] [varchar](200) NULL,
	[ADN] [varchar](3) NULL,
	[DV] [bit] NULL,
	[HC] [bit] NULL,
	[UCR] [varchar](4) NULL,
	[NCIC] [varchar](4) NULL,
	[END] [varchar](10) NULL,
	[UCRTYPE] [int] NULL,
	[NIBRSTYPE] [int] NULL,
	[PRIORITY] [int] NULL,
	[CriminalGangActivity1] [int] NULL,
	[CriminalGangActivity2] [int] NULL,
	[CriminalGangActivity3] [int] NULL,
	[LocationType] [int] NULL,
	[UNIQUEKEY] [varchar](22) NOT NULL,
	[FS] [bit] NULL,
	[ORD] [bit] NULL,
	[ATTY_NO] [varchar](15) NULL,
	[COURT_NO] [varchar](25) NULL,
	[CITATION] [bit] NULL,
	[WRITATT] [bit] NULL,
	[DOM_VIO_INJ] [bit] NULL,
	[ORDER_ARR] [bit] NULL,
	[XMITSORTDATE] [varchar](19) NULL,
	[ARR_INDICATE] [int] NULL,
	[PC] [bit] NULL,
	[CAPIAS] [bit] NULL,
	[AC] [bit] NULL,
	[BW] [bit] NULL,
	[FW] [bit] NULL,
	[PW] [bit] NULL,
	[JUVPU] [bit] NULL,
	[ACTIVITY] [varchar](1) NULL,
	[DRUGTYPE] [varchar](1) NULL,
	[AMOUNT] [varchar](30) NULL,
	[SECTION] [varchar](15) NULL,
	[TRANSMITTED] [bit] NULL,
	[DATE_ISSUED] [datetime] NULL,
	[BONDAMT] [money] NULL
) ON [PRIMARY]

Thank you!!

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-05-03 : 11:22:38


Do the grouping on Arresto and FSN


Select Arresto ,FSN,cout(1) from table
group by  Arresto ,FSN

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-03 : 11:22:50

Did you mean that you want to update the value of COUNTS in the table? Would this work? (first run the select rather than the update and inspect the results to make sure that it is doing what you want it to do)

;WITH cte AS
(
	SELECT ARRESTNO,FSN,COUNTS,
		COUNT(*) OVER (PARTITION BY ARRESTNO,FSN) AS NewCounts
	FROM
		[dbo].[ARR_AFSS]
)
SELECT * FROM cte;
--UPDATE cte SET COUNTS = NewCOUNTS;

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-05-03 : 11:26:56

Bingo sunita. Thank you.

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-05-03 : 11:36:22

Except i'm confused as to this new column "NewCounts". It exists during the select but for the update, it does not, so there's nothing to pull in. I'm manipulating something wrong with your query i think.

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-05-03 : 11:41:28

disregard. got it. thanks again

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-03 : 12:25:25

quote:
Originally posted by WJHamel

disregard. got it. thanks again

I knew if I ignore it long enough you would resolve the problem

Just kidding - I saw this only now. Did you have both the SELECT and UPDATE statements uncommented?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-05-03 : 12:50:02

Are you creating a trigger to constantly update the count? If not how will you maintain it? Maybe you should re-think storing aggregates in the base table?

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-05-03 : 13:49:42

This is for a data conversion for a customer. Our front end app calculates the COUNTS field as they enter new warrant information in relation to the charges. So as they enter new data, the COUNTS column is populated automatically. Because i'm loading their old data from the back end, the calculations done on the front end can't occur and i have to force a value in the COUNTS column.

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-05-03 : 15:44:36

Sunita. Ha! That IS usually how it works. Yes, i did have them both commented out. Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources