Solved

Create a subgroup by a 2nd field using mysql

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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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.​
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 count occurrences of each item in an array.

706 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now