Need help with Count()

[sparkbyte]

I have a script that gets the the current days processed records, but I would like a field that has the count of files processed.

Ultamtely I will need a SP that will take in the user ID as a param and show totals for each user.

The current SQL Code I have is thus.

SELECT     TOP (100) PERCENT dbo.tblTrackingTable.Tracking_ID, dbo.tblTrackingTable.EmployeeID, 
                      dbo.tblEmployee.EmployeeFN + ' ' + dbo.tblEmployee.EmployeeLN AS EmployeeFullName, dbo.tblTrackingTable.MachineName, 
                      dbo.tblTrackingTable.FileNumber AS NumOfFiles, UPPER(dbo.tblTrackingTable.BoxNumber) AS BoxNumber, UPPER(dbo.tblTrackingTable.FileNumber) 
                      AS FileNumber, dbo.tblTrackingTable.TrackingDate
FROM         dbo.tblTrackingTable FULL OUTER JOIN
                      dbo.tblEmployee ON dbo.tblTrackingTable.EmployeeID = dbo.tblEmployee.EmployeeID
WHERE     (dbo.tblTrackingTable.TrackingDate IS NOT NULL) AND (dbo.tblTrackingTable.EmployeeID IS NOT NULL) AND (dbo.tblTrackingTable.FileNumber <> '') 
                      AND (dbo.tblTrackingTable.BoxNumber <> '') AND (dbo.tblTrackingTable.FileNumber <> '.BOX.END.') AND 
                      [b](dbo.tblTrackingTable.FileNumber = COUNT(dbo.tblTrackingTable.FileNumber))[/b]
ORDER BY dbo.tblTrackingTable.TrackingDate, dbo.tblTrackingTable.BoxNumber

This was cut/paste'd from SQL Designer.

Thanks

John Fuhrman

 

[RiverGuy]

Can you list of the columns you want returned in the resultset? What you have posted above doesn't seem to lend itself well to a count because you are showing the Tracking_ID.

 

[djj55]

At first glace I would say you need a subquery that calculates the counts.


djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!

 

[sparkbyte]

EmployeeID, EmployeeFullName,MachineName,BoxNumber,FileNumber,TrackingDate

Should be the columns in the result set.

report like

Toady's Tracking report for EmployeeFullName
Total Files Processed 12

BoxNumber SubTotal 4
FileNumber / TrackingDate / EmployeeFullName
FileNumber / TrackingDate / EmployeeFullName
FileNumber / TrackingDate / EmployeeFullName
FileNumber / TrackingDate / EmployeeFullName
BoxNumber SubTotal 4
FileNumber / TrackingDate / EmployeeFullName
FileNumber / TrackingDate / EmployeeFullName
FileNumber / TrackingDate / EmployeeFullName
FileNumber / TrackingDate / EmployeeFullName
BoxNumber SubTotal 4
FileNumber / TrackingDate / EmployeeFullName
FileNumber / TrackingDate / EmployeeFullName
FileNumber / TrackingDate / EmployeeFullName
FileNumber / TrackingDate / EmployeeFullName

Thanks

John Fuhrman

 

[RiverGuy]

You should be able to produce the groupings and counts in your reporting software. I would take out the (dbo.tblTrackingTable.FileNumber = COUNT(dbo.tblTrackingTable.FileNumber)) part of your query, return the detail rows, and group in the front end.

 

[sparkbyte]

I was trying to do it in Access 2003 but was having difficulties.

Does anyone know of an example??



Thanks

John Fuhrman

 

[sparkbyte]

Why can't I do something like this??

Although the count portion of the Query isn't working yet.

SELECT     TOP (100) PERCENT 
	
--	dbo.tblTrackingTable.Tracking_ID, 
	dbo.tblTrackingTable.EmployeeID, 
	dbo.tblEmployee.EmployeeFN, 
	dbo.tblEmployee.EmployeeLN,
	dbo.tblEmployee.EmployeeFN + ' ' + dbo.tblEmployee.EmployeeLN AS EmployeeFullName, 
	dbo.tblTrackingTable.MachineName,
	UPPER(dbo.tblTrackingTable.BoxNumber) AS BoxNumber, 
	UPPER(dbo.tblTrackingTable.FileNumber) AS FileNumber, 
    dbo.tblTrackingTable.TrackingDate,
	[b][red]Count(BoxNumber) as TotalBoxNum[/red][/b]

FROM  
	dbo.tblTrackingTable

FULL OUTER JOIN	dbo.tblEmployee 
	ON dbo.tblTrackingTable.EmployeeID = dbo.tblEmployee.EmployeeID

WHERE   (dbo.tblTrackingTable.TrackingDate IS NOT NULL) 
		AND (dbo.tblTrackingTable.EmployeeID IS NOT NULL) 
		AND (dbo.tblTrackingTable.FileNumber <> '') 
        AND (dbo.tblTrackingTable.BoxNumber <> '') 
		AND (dbo.tblTrackingTable.FileNumber <> '.BOX.END.')


Group By 
	dbo.tblTrackingTable.EmployeeID, 
	dbo.tblEmployee.EmployeeFN, 
	dbo.tblEmployee.EmployeeLN,
	dbo.tblTrackingTable.MachineName,
    dbo.tblTrackingTable.TrackingDate,
	dbo.tblTrackingTable.BoxNumber, 
	dbo.tblTrackingTable.FileNumber

Thanks

John Fuhrman

 

[gmmastros]

Can you show some sample data (from both tables) and the expected results?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sparkbyte]

CREATE TABLE [dbo].[tblEmployee](
	[EmployeePK] [int] IDENTITY(1,1) NOT NULL,
	[EmployeeID] [nvarchar](15) NULL,
	[EmployeeFN] [nvarchar](50) NULL,
	[EmployeeMI] [nvarchar](50) NULL,
	[EmployeeLN] [nvarchar](50) NULL,
	[EmployeeDept] [nvarchar](50) NULL,

1 jffuhrma John F Fuhrman OIT
2 jnsmith Linda N Smith Mailroom

CREATE TABLE [dbo].[tblTrackingTable](
	[Tracking_ID] [int] IDENTITY(1,1) NOT NULL,
	[EmployeeID] [varchar](50) NULL,
	[MachineName] [varchar](20) NULL,
	[BoxNumber] [varchar](45) NOT NULL,
	[FileNumber] [varchar](25) NOT NULL,
	[TrackingDate] [datetime] NULL,
 CONSTRAINT [PK_tblTrackingTable] PRIMARY KEY CLUSTERED

19 jffuhrma WS1 1z4a000r0000000000 a21321321 3/31/2010 4:37:27 PM
20 jffuhrma WS1 1z4a000r0000000000 a32132132 3/31/2010 4:37:28 PM



This comes close but just not sure how to create a report in MS Access 2003 that does a running subtotal like the example above.

USE [MailroomTracking]

Select Top 100
	dbo.tblTrackingTable.EmployeeID, 
	dbo.tblEmployee.EmployeeFN, 
	dbo.tblEmployee.EmployeeLN,
	dbo.tblEmployee.EmployeeFN + ' ' + dbo.tblEmployee.EmployeeLN AS EmployeeFullName, 
	BoxNumber,
	Count(BoxNumber) as TotalBoxNum

From 
	dbo.tblTrackingTable

FULL OUTER JOIN	dbo.tblEmployee 
	ON dbo.tblTrackingTable.EmployeeID = dbo.tblEmployee.EmployeeID

WHERE   (dbo.tblTrackingTable.TrackingDate IS NOT NULL) 
		AND (dbo.tblTrackingTable.EmployeeID IS NOT NULL) 
		AND (dbo.tblTrackingTable.FileNumber <> '') 
        AND (dbo.tblTrackingTable.BoxNumber <> '') 
		AND (dbo.tblTrackingTable.FileNumber <> '.BOX.END.')

Group By 
	dbo.tblTrackingTable.EmployeeID,
	dbo.tblTrackingTable.BoxNumber,
	dbo.tblEmployee.EmployeeFN, 
	dbo.tblEmployee.EmployeeLN

Having Count(BoxNumber) >= 1
Order by TotalBoxNum desc

Thanks

John Fuhrman

 

[gmmastros]

Based on the sample data, what output do you want to see?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sparkbyte]

I figured out the report problem.

And learned how the Count in SQL works much better.

The last query I posted was the clicher on not being able to get a query to show what I wanted. Although I did work out how to get it to show the BoxNumber totals propperly. You just can not add all the fields I wanted to show and still have the BoxNumber totals.

Thanks EVERYONE!!

Thanks

John Fuhrman