COUNT items within a result set

Question

COUNT items within a result set

JJR333

Default port

Points: 1469
More actions
May 15, 2014 at 11:42 am

#307954

I want to add a column to this query which displays the number of times each MwID is present in the result set.
This number is needed to divide into FteHrsAVG.
Any suggestions are welcome!
TIA,
Julian
SELECTMwID,NiveauZPT,AfdelingZPT,FTEHrsAVG
FROMDRPDATA
WHERELocatieCode='sb' AND
DATEPART(iso_week,Begindatum) = 1 AND
AfdelingZPT = 'a03' AND
FlexVast = 'VAST'
ORDER BY MwID

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

JJR333 (5/15/2014)
I want to add a column to this query which displays the number of times each MwID is present in the result set.
This number is needed to divide into FteHrsAVG.
Any suggestions are welcome!
TIA,
Julian
SELECTMwID,NiveauZPT,AfdelingZPT,FTEHrsAVG
FROMDRPDATA
WHERELocatieCode='sb' AND
DATEPART(iso_week,Begindatum) = 1 AND
AfdelingZPT = 'a03' AND
FlexVast = 'VAST'
ORDER BY MwID

I can come up with about 6 different possibilities. They all depend on what your actual table looks like. In order to help we will need a few things:

1. Sample DDL in the form of CREATE TABLE statements

2. Sample data in the form of INSERT INTO statements

3. Expected results based on the sample data

Please take a few minutes and read the first article in my signature for best practices when posting questions.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

gbritton1 SSCertifiable Points: 6522 More actions · Answer 2

Add a new column to the select:

Count(*) over(partition by MwID) as MwID_Count

JJR333 Default port Points: 1469 More actions · Answer 3

@gbritton1: thanks that worked!

The code below gives me the correct results.

How do you get the sum of Hrs for each NiveauZPT?

An aggregate cannot be used on a Windowed function. (error msg)

Cheers,

Julian

SELECTNiveauZPT,FTEHrsAVG/(Count(*) over(partition by MwID)) AS Hrs

FROMDRPDATA

WHERELocatieCode='sb' AND

DATEPART(iso_week,Begindatum) = 1 AND

AfdelingZPT = 'a03' AND

FlexVast = 'VAST'

ORDER BY MwID

gbritton1 SSCertifiable Points: 6522 More actions · Answer 4

Add another windowed function:

sum(hours) over (partition by NiveauZPT)

(Interresant om het nederlands in de schema te zien!)

JJR333 Default port Points: 1469 More actions · Answer 5

I am half Dutch...

The result set looks like the table below.

Now I would like to sum the Hrs for each Niveau.

Any ideas?

Thanks,

Julian

NIVHRS

37

39

34

27,5

23,11111111111111

2+6

218

2+3

38

28

23,33333333333333

310,6666666666667

35,33333333333333

95,14285714285714

29,33333333333333

23,5

JJR333 Default port Points: 1469 More actions · Answer 6

@gbritton1, thank you for pointing me in the right direction.

Ended up with using the code below.

Cheers,

Julian

-- JJR 20140508 ADDED UPDATE USING OVER PARTITION, MUCH FASTER

;WITH Updater AS

(

SELECT

FTEHrsAVG,

NewFTEHrsAVG = AVG(CASE

WHEN FlexVast = 'VAST'

THEN FTEHrs

ELSE NULL

END)

OVER(PARTITION BY MWID, [Org eenheid code], DATEPART(ISO_WEEK,BEGINDATUM))

FROM DRPDATA o

)

UPDATE Updater SET FTEHrsAVG = NewFTEHrsAVG

-- /JJR

/*

Return Rows per Week, per MWID and per AfdelingZPT (or Org eenheid code)

FTEHrsAVG divided by RowsPerWeek gives contract hours for the week, for the AfdelingZPT

ONLY if one sums per Week, per AfdelingZPT

*/

;WITH Updater AS

(

SELECT

RowsPerWeek,

NewRowsPerWeek =

count(CASE

WHEN FlexVast = 'VAST'

THEN FTEHrs

ELSE NULL

END)

OVER(PARTITION BY MWID, [Org eenheid code], DATEPART(ISO_WEEK,BEGINDATUM))

FROM DRPDATA

--WHERE EXISTS

)

UPDATE Updater SET RowsPerWeek = NewRowsPerWeek