Solved

Create a subgroup by a 2nd field using mysql

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
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 count occurrences of each item in an array.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

867 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

12 Experts available now in Live!

Get 1:1 Help Now