al4629740
asked on
Get the count of a specific record
Below is my query in sql. The output that I get contains the sector name in every single record. however I would like to get a count of how many in each sector. For example
Output
RegID LastName FirstName Hours Program Agency Sector
14 Adfs fdsa 3.00 CSW Administrator Attorney
35 Dfsa 543 3.00 CSW Administrator Business Representative
14 Adfs fdsa 3.00 CSW Administrator Attorney
35 Dfsa 543 3.00 CSW Administrator Business Representative
The above query should only show how many of each sectors were present. In this case 2 Attorneys and 2 Business Reps.
How can I modify the query below to get that result?
Output
RegID LastName FirstName Hours Program Agency Sector
14 Adfs fdsa 3.00 CSW Administrator Attorney
35 Dfsa 543 3.00 CSW Administrator Business Representative
14 Adfs fdsa 3.00 CSW Administrator Attorney
35 Dfsa 543 3.00 CSW Administrator Business Representative
The above query should only show how many of each sectors were present. In this case 2 Attorneys and 2 Business Reps.
How can I modify the query below to get that result?
SELECT r.RegID,r.[LastName], r.[FirstName], SUM(h.hours) as Hours, h.Program, o.[Agency],r.Sector
FROM tblOrgRegistrations AS r
LEFT JOIN tblOrgHours AS h
ON h.RegID = r.RegID
AND Month(ActivityDate) = 7
LEFT JOIN tblOrgActivities AS a
ON h.ActivityID = a.ActivityID
LEFT JOIN tblOrgProfile AS o
ON h.AgencyID = o.AgencyID Where o.AgencyID = '74' And r.Board = 1 And Hours > 0 And a.ActivityName = 'Board Meeting'
Group By r.RegID, o.[Agency],h.Program, r.FirstName, r.LastName, r.Sector order by r.LastName
To reduce the number of rows produced by a group by, reduce the number of fields that you group by.
Here all you need to do is remove "r.sector" from the group by clause, and then change "r.sector" in the select clause to include the aggregate function: count()
Here all you need to do is remove "r.sector" from the group by clause, and then change "r.sector" in the select clause to include the aggregate function: count()
SELECT
r.RegID
, r.[LastName]
, r.[FirstName]
, SUM(h.hours) AS Hours
, h.Program
, o.[Agency]
, count(r.Sector) as Sector_Count -- changed
FROM tblOrgRegistrations AS r
INNER JOIN tblOrgHours AS h -- inner join
ON h.RegID = r.RegID
AND MONTH(ActivityDate) = 7
INNER JOIN tblOrgActivities AS a -- inner join
ON h.ActivityID = a.ActivityID
INNER JOIN tblOrgProfile AS o -- inner join
ON h.AgencyID = o.AgencyID
WHERE o.AgencyID = '74'
AND r.Board = 1
AND h.Hours > 0 -- use the alias
AND a.ActivityName = 'Board Meeting'
GROUP BY
r.RegID
, o.[Agency] -- remove r.Sector from the group by
, h.Program
, r.FirstName
, r.LastName
-- previously "r.Sector" had been included
ORDER BY
r.LastName
;
I know you have another question open regarding the joins, but as this query stands right now they are all inner joins.
ASKER
This output is not what I'm looking for
I would like to have an output from the output above such as
Sector Count
Businessman 2
Attorney 1
etc....
RegID LastName FirstName Hours Program Agency Sector_Count
14 Adfs fdsa 3.00 CSW Administrator 3
35 Dfsa 543 3.00 CSW Administrator 3
I would like to have an output from the output above such as
Sector Count
Businessman 2
Attorney 1
etc....
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window