12 Replies Latest reply on Jun 7, 2006 7:19 AM by paross1

    2 group by

    RyanG24 Level 1
      Hi i have this query
      SELECT Team, Year, Result
      FROM TableOneTable
      GROUP BY Team, Year
      ORDER BY Year

      in my output is get

      TeamOne
      2006
      TeamOne
      2005
      TeamTwo
      2006
      TeamTwo
      2005

      which works but i need to format my output like this

      TeamOne
      2006
      2005
      TeamTwo
      2006
      2005

      not sure what i need to do to get this to change?

      can you help
      Thanks
        • 1. Re: 2 group by
          Fernis Level 3
          ORDER BY Team, Year
          • 2. 2 group by
            RyanG24 Level 1
            ok i understand that, but i have a Q of Q not sure how this is done in this situation

            <cfquery name="getYear2344" datasource="Cricket">
            SELECT
            BB.TeamName,
            BB.MatchType,
            BB.MatchYear,
            BB.MatchesPlayed

            FROM
            (SELECT
            TList.TeamName,
            AA.matchtype,
            year (AA.DateofMatch) AS MatchYear,
            COUNT(DISTINCT AA.MatchID) AS MatchesPlayed

            FROM
            BattingStats AA, TeamNames TList
            WHERE
            1=1 AND PlayersID = '#client.Player#' AND AA.MatchType = TList.ID

            GROUP BY
            MatchType
            ORDER BY MatchType, MatchYear DESC

            )AS BB
            </cfquery>
            • 3. Re: 2 group by
              Dan Bracuk Level 5
              First, you don't have a Q of Q, you have a subquery in your where clause. Q of Q is a Cold Fusion term, not an sql term.

              Next, the order by clause serves no useful purpose in your subquery. It should be outside your subquery.

              Third, you don't even need a subquery.

              Fourth, you have to group by matchyear.

              Fifth, in your query, having where 1 = 1 serves no useful purpose.

              Once you get your query to run, you can use the group attribute in cfoutput to get the grouping you want.
              • 4. Re: 2 group by
                mike.short
                From your initial query:

                SELECT Team, Year, Result
                FROM TableOneTable
                GROUP BY Team, Year
                ORDER BY Year

                you have all the information you need to do what you want, but you'll want to order by Team, Year, and add DESC to the Year, so the order is descending.

                The CF code would look something like this:

                <cfquery name="qQ" datasource="dsn">
                SELECT Team, Year, Result
                FROM TableOneTable
                GROUP BY Team, Year
                ORDER BY Team, Year DESC
                </cfquery>

                <cfoutput query="qQ" group="Team">
                #qQ.Team#<br>
                <cfoutput>
                #qQ.Year#
                </cfoutput>
                </cfoutput>

                • 5. Re: 2 group by
                  mike.short Level 1
                  Dan's comment about 1=1 having no value needs some illumination. The expression 1=1 in a WHERE clause can be very useful if the rest of the WHERE clause is dynamically generated, and there are cases where none of the remaining clauses will appear. Something like this:

                  <cfquery name="qQ" datasource="dsn">
                  SELECT * from TABLE
                  WHERE 1=1
                  <cfif form.year LT "2006">
                  AND Hist_row = 'Y'
                  </cfif>
                  </cfquery>

                  I made this up, but hopefully you get the point.
                  • 6. Re: 2 group by
                    RyanG24 Level 1
                    ok i have a case clause in my query which i need aswell, but this dosnt work when i use Q of Q, Thats why i think i need a subquery is this correct?

                    SELECT Team, Year, Result, M1, M2
                    FROM TableOneTable
                    GROUP BY Team, Year
                    ORDER BY Year

                    <cfquery name="qQ" datasource="dsn">
                    SELECT Team, Year, Result,
                    (CASE WHEN M1 = 0 THEN 0 ELSE M1 / M2 END) AS "SR"
                    FROM TableOneTable
                    GROUP BY Team, Year
                    ORDER BY Team, Year DESC
                    </cfquery>


                    • 7. Re: 2 group by
                      Dan Bracuk Level 5
                      This
                      SELECT Team, Year, Result,
                      (CASE WHEN M1 = 0 THEN 0 ELSE M1 / M2 END) AS "SR"
                      FROM TableOneTable
                      GROUP BY Team, Year
                      ORDER BY Team, Year DESC

                      looks ok. The group by clause is not necessary. The brackets around your case statement is also not necessary. Notice the lack of subqueries?
                      • 8. 2 group by
                        RyanG24 Level 1
                        OK I AM STILL GETTING ERRORS, but yes the lack of subqueries is good, think the error is coming because of the sum?

                        Can i do something like this

                        case when SUM(M) = 0 THEN SUM(M) ESLE SUM(M) / SUM(D) AS AverageMs

                        SELECT Team, Year, Result, SUM(M) AS M1, SUM(D) AS M2
                        (CASE WHEN M1 = 0 THEN 0 ELSE M1 / M2 END) AS "SR"
                        FROM TableOneTable
                        GROUP BY Team, Year
                        ORDER BY Team, Year DESC

                        Also when i have this working i have 5 queries all the same as above but with different group by clauses, can i have one main query and do 5 Q of Q , i have tryed this but i get errors from the Q of Q group by clause, is this possable?
                        • 9. Re: 2 group by
                          Dan Bracuk Level 5
                          quote:

                          Originally posted by: RyanG24
                          OK I AM STILL GETTING ERRORS, but yes the lack of subqueries is good, think the error is coming because of the sum?

                          Can i do something like this
                          case when SUM(M) = 0 THEN SUM(M) ESLE SUM(M) / SUM(D) AS AverageMs


                          Something like that will work. Just spell else correctly (I almost always type esle the first time too) and remember the keyword end to tell your db that your case statement is finished.

                          A good strategy when you are having trouble with a query, or any chunk of code for that matter, is to start with something simple, and build it up in small increments.
                          • 10. Re: 2 group by
                            RyanG24 Level 1
                            Ok thanks that works, now if i have the query below can i do a QofQ with a Group Clause?

                            so this is what i think it would look like...

                            <cfquery name="GETALL" datasource="Users">
                            SELECT Team, Year, Result, SUM(M) AS M1, SUM(D) AS M2
                            case when SUM(M) = 0 THEN SUM(M) ELSE SUM(M) / SUM(D) END AS AverageMs
                            FROM TableOneTable
                            GROUP BY Team
                            </cfquery>

                            QofQ
                            <cfquery name="GETALL" dbtype="query">
                            SELECT Year, M2, AverageMs
                            FROM GETALL
                            GROUP BY Year
                            </cfquery>

                            i get this error
                            Query Of Queries runtime error.
                            The column "M2" is invalid in the "SELECT list" clause because it is not contained in either an aggregate function, or the GROUP BY clause.
                            • 11. Re: 2 group by
                              Dan Bracuk Level 5
                              Once you have run your getall query, why do you need a Q of Q at all? Also, generally speaking, having a group by clause when there are no aggregates (count, sum, etc) in the select clause is wasted typing. Apparently, in Q of Q, it throws an error.

                              Have you ever read the cfoutput pages in the cfml reference manual? There is a group attribute you may find useful.
                              • 12. 2 group by
                                paross1 Level 2
                                If you are trying to sum the M2 and AverageMs values by year in the Q-of-Q, wouldn't you SUM() them there then group by, since your initial query groups by team? Of course, looking at what you said that you wanted as an output, the reply from Mike.short (using the GROUP attribute in CFOUTPUT) is most likely the answer that you should consider.

                                <cfquery name="GETALL" dbtype="query">
                                SELECT Year, SUM(M2), SUM(AverageMs)
                                FROM GETALL
                                GROUP BY Year
                                </cfquery>

                                Phil