2 group by

[RyanG24]

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

[Fernis]

ORDER BY Team, Year

[RyanG24]

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>

[Dan_Bracuk]

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.

[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>

[mike_short]

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.

[RyanG24]

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>

[Dan_Bracuk]

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?

[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

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?

[Dan_Bracuk]

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.

[RyanG24]

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.

[Dan_Bracuk]

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.

[paross1]

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