Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 131
  • Last Modified:

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

0
al4629740
Asked:
al4629740
  • 2
1 Solution
 
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
 
PortletPaulCommented:
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
 
PortletPaulCommented:
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

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now