Solved

Get the count of a specific record

Posted on 2014-07-24
4
117 Views
Last Modified: 2014-08-01
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
Comment
Question by:al4629740
  • 2
4 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40217545
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40218529
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
 

Author Comment

by:al4629740
ID: 40230768
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
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40230865
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question