Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 738
  • Last Modified:

Create a subgroup by a 2nd field using mysql

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
aberns
Asked:
aberns
  • 2
  • 2
2 Solutions
 
tusharkanvindeCommented:
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
 
abernsAuthor Commented:
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
 
tusharkanvindeCommented:
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
 
Mark WillsTopic AdvisorCommented:
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
 
abernsAuthor Commented:
Thanks to both for your advice. I'm sitll considering which solution to use but both had merit. Thanks! Audrey
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now