Solved

Get the count of a specific record

Posted on 2014-07-24
4
121 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

732 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