Solved

Create a subgroup by a 2nd field using mysql

Posted on 2008-06-14
5
733 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
[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
  • 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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

636 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