?
Solved

Find count within a query

Posted on 2014-04-04
4
Medium Priority
?
267 Views
Last Modified: 2014-04-05
I have the following sql query

SELECT Max(Text4)Text4,Max(Text5)Text5,Max(Combo11)Combo11, Sum(Total)TotalsperStudent FROM AttendanceGrid INNER JOIN tblRegistration ON AttendanceGrid.[ID]=tblRegistration.[ID] Where  AttendanceGrid.Fiscal = '2014'  Group by tblRegistration.ID HAVING Sum(Total)>0 

Open in new window


The output looks like this:

Text4	Text5	Combo11	TotalsperStudent
Guthrie	Tionne	Mid Austin	   129
Rodriguez	Eleany	LOS	52
Meeks	Quiana	Major Adams	   53
Colley	Kayla	Mid Austin	     236

Open in new window


How can I change the query to get the following output.  I want to find out how many records there are per group (Combo11)?

My desired output based on the above results would be:

Mid Austin   2
LOS                1
Major Adams  1

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 69

Expert Comment

by:Scott Pletcher
ID: 39979372
SELECT Combo11, COUNT(DISTINCT Text4+Text5) Recs_Per_Group
FROM AttendanceGrid
INNER JOIN tblRegistration ON AttendanceGrid.[ID]=tblRegistration.[ID]
Where  AttendanceGrid.Fiscal = '2014'  
Group by Combo11
0
 
LVL 41

Accepted Solution

by:
Sharath earned 2000 total points
ID: 39979423
SELECT Combo11, 
       COUNT(*) Cnt 
  FROM (SELECT MAX(Combo11)Combo11 
          FROM AttendanceGrid 
               INNER JOIN tblRegistration 
                       ON AttendanceGrid.[ID] = tblRegistration.[ID] 
         WHERE AttendanceGrid.Fiscal = '2014' 
         GROUP BY tblRegistration.ID 
        HAVING SUM(Total) > 0) T1 
 GROUP BY Combo11 

Open in new window

0
 

Author Comment

by:al4629740
ID: 39980593
SELECT Combo11,
       COUNT(*) Cnt

What does Cnt stand for or do?
0
 

Author Comment

by:al4629740
ID: 39980603
nevermind....
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

765 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