Solved

Create a subgroup by a 2nd field using mysql

Posted on 2008-06-14
5
726 Views
Last Modified: 2012-06-21
Can you help me modify a MySQL query so that I can "group by" twice? I want to group by CourseNameNo, and then further Group By Section. I can surpress the row in php and just display the section, but I'm wondering if there's a more elegant way. Thanks.
Original Query:
SELECT Count(*) as SummaryTotal,CourseNameNo,ClassSection,Studio FROM SSLI_Referral GROUP BY CourseNameNo,ClassSection having SummaryTotal >1 ORDER BY CourseNameNo,ClassSection
Instead of it looking like this:
<table>
<tr><td>Advanced Integrated ESL BC </td></tr>  
<tr><td>Advanced Integrated ESL CD Studio  </td></tr> 
<tr><td>Advanced Integrated ESL GH Studio   </td></tr>
<tr><td>Advanced Integrated ESL HJ Studio   </td></tr>
<tr><td>Advanced Integrated ESL WAB  </td></tr>
</table>
I want it to look like this:
<table>
<tr><td>Advanced Integrated ESL</td></tr>  
<tr><td> BC </td></tr>  
<tr><td>CD </td></tr> 
<tr><td>GH </td></tr>
<tr><td>HJ  </td></tr>
<tr><td>WAB  </td></tr>
</table>

Open in new window

0
Comment
Question by:aberns
  • 2
  • 2
5 Comments
 
LVL 14

Expert Comment

by:tusharkanvinde
ID: 21787607
You are already grouping by section. The data part should be handled by MySQL, the display part by php. So you will have to do the coding in php.
0
 

Author Comment

by:aberns
ID: 21788544
OK...I was just wondering if I could GROUP BY twice...first by CourseNameNo and then by the Sections within CourseNameNo...like a subquery? i.e. first Group all CourseNameNo's and then group the Sections within the CourseNameNo?

0
 
LVL 14

Accepted Solution

by:
tusharkanvinde earned 70 total points
ID: 21788690
You will have to select distinct CourseNameNo for query 1 which will set up the first loop. Then in the loop, query by Section where CourseNameNo= the CourseNameNo of the 1st loop. That will basically increase the number of calls made to the database server. I feel your current method is better.
0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 55 total points
ID: 21788784
You could always do a union query, where they had the same basic structure, but you decided what was in the "display" name... e.g.

Not sure which columns are which, so using the first two as the columns that could be displayed...

select c.Display_Name from
(
SELECT Count(*) as SummaryTotal, CourseNameNo as Display_Name,CourseNameNo,' ' as ClassSection FROM SSLI_Referral GROUP BY CourseNameNo
union all
SELECT Count(*) as SummaryTotal, ClassSection as Display_Name,CourseNameNo,ClassSection FROM SSLI_Referral GROUP BY CourseNameNo,ClassSection
) c
WHERE c.SummaryTotal >1
ORDER BY c.CourseNameNo,c.ClassSection
0
 

Author Closing Comment

by:aberns
ID: 31467279
Thanks to both for your advice. I'm sitll considering which solution to use but both had merit. Thanks! Audrey
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to count occurrences of each item in an array.

770 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