How do I group my cfquery on multiple columns?

Question

I need to group my query where multiple columns match. For example, group all rows where date, category, and description match. I know how to use cfoutput grouping when grouping on one column, such as:

<cfoutput query="myQry" group="date">
  #date#
  <cfoutput>
    #detail#
  </cfoutput>
</cfoutput>

But, I want to group where multiple columns match, like so:

<cfoutput query="myQry" group="date,category,description">
  #date# #category# #description#
  <cfoutput>
    #detail#
  </cfoutput>
</cfoutput>

I know cfoutput grouping doesn't work like above. So how can I group on multiple columns?

Stalk

Answer 1

You add extra <cfoutput group=""> tags.

<cfoutput query="myQry" group="date">
 <cfoutput group="category">
  <cfoutput group="description">
   #date# #category# #description#
   <cfoutput>
    #detail#
   </cfoutput>
  </cfoutput>
 </cfoutput>
</cfoutput>

Cutting

Thanks. I couldn't get this to work earlier, but I obviously made a mistake because it's working for me now. – Stalk

if you find your groups aren't grouping or look erratic, check your order by clause in your sql. Make sure your grouped columns are also in your order by and in the same order as your group nesting, in this case: SELECT column(s) FROM table(s) ORDER BY date, category, description. – Burnt

Answer 2

Looks like you have an answer with Matt, but in case you are curious of a pure sql approach: this creates a "virtual" column to do the "single" group by, joins the result back to the original table, and uses distinct to get rid of the duplicates. Ugly, but still kind of neat, I think :)

postgres=# create table myTable(col1 int, col2 int, val int);
CREATE TABLE
postgres=#
postgres=# insert into myTable values(1, 1, 1);
INSERT 0 1
postgres=# insert into myTable values(1, 2, 2);
INSERT 0 1
postgres=# insert into myTable values(1, 2, 3);
INSERT 0 1
postgres=# insert into myTable values(2, 1, 4);
INSERT 0 1
postgres=# insert into myTable values(2, 1, 5);
INSERT 0 1
postgres=# insert into myTable values(2, 1, 6);
INSERT 0 1
postgres=# insert into myTable values(2, 2, 7);
INSERT 0 1
postgres=# insert into myTable values(2, 3, 8);
INSERT 0 1
postgres=# insert into myTable values(2, 3, 9);
INSERT 0 1
postgres=# insert into myTable values(2, 3, 10);
INSERT 0 1
postgres=# insert into myTable values(2, 3, 11);
INSERT 0 1
postgres=#
postgres=# select col1, col2, count(*)\
Invalid command \. Try \? for help.
postgres-#   from myTable
postgres-#   group by col1, col2
postgres-#   order by 1, 2;
 col1 | col2 | count
------+------+-------
    1 |    1 |     1
    1 |    2 |     2
    2 |    1 |     3
    2 |    2 |     1
    2 |    3 |     4
(5 rows)


postgres=#
postgres=#
postgres=# select col1 || ',' || col2 AS theGroup
postgres-#       ,count(*) AS theCount
postgres-#   from myTable
postgres-#   group by col1 || ',' || col2
postgres-#   order by 1;
 thegroup | thecount
----------+----------
 1,1      |        1
 1,2      |        2
 2,1      |        3
 2,2      |        1
 2,3      |        4
(5 rows)


postgres=#
postgres=#
postgres=# select distinct a.col1, a.col2, b.theCount
postgres-#   from myTable a
postgres-#       ,( select col1 || ',' || col2 AS theGroup
postgres(#                ,count(*) theCount
postgres(#            from myTable
postgres(#            group by col1 || ',' || col2 ) b
postgres-#   where a.col1 || ',' || a.col2 = b.theGroup
postgres-#   order by 1, 2;
 col1 | col2 | thecount
------+------+----------
    1 |    1 |        1
    1 |    2 |        2
    2 |    1 |        3
    2 |    2 |        1
    2 |    3 |        4
(5 rows)


postgres=#

Correy