Solved

Create a subgroup by a 2nd field using mysql

Posted on 2008-06-14
5
732 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Node.js 11 81
embadded search engine in website 4 46
replacate wordpress websites 3 23
How to convert PHP array into string ? 16 24
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
This article discusses how to implement server side field validation and display customized error messages to the client.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

734 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