On Wed, Feb 16, 2011 at 03:24:51PM -0600, Joe Bennett scratched on the wall: > Hi all, > > I am trying to locate any examples (if possible) that can clue me in on > taking column a and appending column b then do a pivot/group by to get the > total for each unique values... I'll try to demonstrate the base data: > > *Column_1* *Column_2* > Value A Value C > Value B Value A > Value C Value D > Value D Value E > > Now, I can pivot/group on one or the other column via [select Column_1, > count(Column_1) from Table group by Column_1] but and looking for a way to > create something like a 'virtural' column by appending Column_1 and > Column_2... Not sure if there is an easier way than creating a temp table > with the data and pivoting that way...????
SELECT Column_1||Column_2, count(*) FROM Table GROUP BY 1; "GROUP BY" and "ORDER BY" will both accept numbers. If given a number, it is used as a result column index. This query computes the append you want, and then groups by that result column. In this specific case, you could also just group over both columns, since the unique value you want depends on the values of those two columns. For example: SELECT Column_1||Column_2, count(*) FROM Table GROUP BY Column_1, Column2; Many databases will not allow this, but SQLite does. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
