OK, this looks like it concatenated Column_1 and Column_2 and returns the count of the new unique concatenated pair? What I am looking for (and I apologize for not being clear) is a list of the unique values (Column 1 and 2 appended) and their count... I'll try to demonstrate the expected example from the table example I gave below:
*Result* *Count(result)* Value A 2 Value B 1 Value C 2 Value D 2 Value E 1 -Joe On Wed, Feb 16, 2011 at 3:40 PM, Jay A. Kreibich <j...@kreibi.ch> wrote: > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
