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?


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

Open in new window

al4629740Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
I can't test this as I'm not connected to the data source, but using a subquery give this a whirl..
SELECT r.RegID,r.[LastName], r.[FirstName], SUM(h.hours) as Hours, h.Program, o.[Agency],r.Sector, sc.the_count
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 
   JOIN (
      SELECT Sector, COUNT(sector) as the_count
      FROM tblOrgRegistrations
      GROUP BY Sector) sc ON r.Sector = sc.Sector
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, sc.the_count
order by r.LastName

Open in new window

0
PortletPaulfreelancerCommented:
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()
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
;

Open in new window

I know you have another question open regarding the joins, but as this query stands right now they are all inner joins.
0
al4629740Author Commented:
This output is not what I'm looking for

RegID	LastName	FirstName	Hours	Program	Agency	Sector_Count
14	Adfs	fdsa	3.00	CSW	Administrator	3
35	Dfsa	543	3.00	CSW	Administrator	3

Open in new window


I would like to have an output  from the output above such as

Sector                          Count
Businessman                  2
Attorney                          1
etc....
0
PortletPaulfreelancerCommented:
The query "structure" remains the same, the details of that structure alter. This only lists Sector and only groups by Sector and orders by Sector. The remaining parts are the same.
SELECT
      r.Sector
    , count(*) as Sector_Count
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.Sector
ORDER BY
      r.Sector
;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.