Solved

Get the count of a specific record

Posted on 2014-07-24
4
122 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 66

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 49

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 49

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

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

688 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