How to add a COUNT value against unique name returns

Question

Post reply

How to add a COUNT value against unique name returns

the.lost.scott

SSC Rookie

Points: 25
More actions
September 27, 2017 at 3:17 am

#146471

Hi all,
My first post in this forum and I am hoping someone here can help!
I am trying to write in a calculation into a script that has the total count of Employees in a Store in a separate column, but have that counted number appear against all the employees located in each unique store name on each row that the Store & Employee appears

SELECT DISTINCT
   STORE.StoreID AS 'Store ID'
  ,STORE.Name AS 'Store Name'
,EMPL.EmployeeID AS 'Empl. ID'
,EMPL.LastName AS 'Empl. Last Name'
,EMPL.FirstName AS 'Empl. First Name'
-- ,COUNT OF EMPLOYEES AT EACH STORE.Name
FROM  DB1_GroupStores AS STORE LEFT OUTER JOIN DB1_Relationship AS REL_STORE ON REL_STORE.Source_InstanceId = STORE.InstanceID
        AND REL_STORE.DatasetID = 'SDB.Asset'
        AND REL_STORE.Source_ClassID = 'Group_Stores'
        AND REL_STORE.Destination_ClassID = 'Group_Employees'

LEFT OUTER JOIN DB1_GroupEmployees AS APP ON REL_STORE.Destination_InstanceId = EMPL.InstanceID
        AND EMPL.DatasetID = 'SDB.Asset'
        AND EMPL.DatasetID = REL_STORE.DatasetId
WHERE  STORE.DatasetID = 'SDB.Asset'
ORDER BY STORE.Name
Many thanks in advance for the help,
Scott

Viewing 4 posts - 1 through 3 (of 3 total)

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

the.lost.scott SSC Rookie Points: 25 More actions · Answer 1

Would I write the above as a Main Query and then the count into a Sub-Query?

Thom A SSC Guru Points: 99126 More actions · Answer 2

Welcome to SSC.

When posting T-SQL questions, you need to ensure you provide DDL, DLM and expected outputs. We don't have access to your data, Server, etc, so simply your T-SQL statement in solitude doesn't help us help you. We can't run your T-SQL, and so any answer we give would be guess work (at best). There's a link in my signature on how to post T-SQL questions.

Speaking of guess work, the answer might be that you just need to use the OVER Clause. I'm not sure what the meaning the the DISTINCT is in your query though, so this may not give the results you need. The fact you have it in there implies duplications, and if that is the case then the COUNT is likely going to be wrong. Anyway, my guess would be:
COUNT(EMPL.EmployeeID) OVER (PARTITION BY STORE.StoreID) AS StoreEmployees
If that doesn't work/doesn't give the expected result, please post back with DDL, DLM and your expected results so that we can provide a more informed solution.

Thanks.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Thom A SSC Guru Points: 99126 More actions · Answer 3

Also, what is the need for your LEFT JOIN to the table DB1_GroupEmployees? You don't reference the table in your SELECT or WHERE clause, and the fact that it's a LEFT JOIN means all it does is slow your query down without adding anything meaningful to, or filtering the returned resultset. This could also be why you have a DISTINCT, as if you get multiple rows returned from the table DB1_GroupEmployees it'll cause the row to be duplicated in the result set (you won't be able to see the difference between the 2 rows, as you're not displaying any columns from DB1_GroupEmployees). You should consider removing that JOIN from your query.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk