How to Reorder Columns using SQL
It's easy to remove columns from your data that you don't want to see with the Select Columns transform. However, if you would like to change the order of those columns, you will need to use SQL. But don't worry—it's one of the easiest things to do!
In this report I have grouped the data and then added a couple calculated columns that showed up at the end ("Bounce Rate" and "Pageviews per Session").
I'd like to place those new columns earlier in the data, so I will add a new SQL transform. The default SQL is "SELECT * FROM report;" The asterisk means it will include all of the columns in the order they already are.
I will remove that asterisk and add in each column in the order I want them. Make sure each one is surrounded by double quotes (") and separated by a comma (outside of the quotes). You can even double-click each column name in the box at the right to include them in the data. (This will include the quotation marks, but not the commas.)
So in my example I will enter this:
SELECT
"ga:date",
"Sum of ga:sessions",
"Pageviews per session",
"Sum of ga:pageviews",
"Bounce rate",
"Sum of ga:bounces"
FROM report;
And I will end up with this in the report:
Extra Tip: Removing or Renaming Columns
If you do not want a column to show up, you can just remove it from the list. You can also rename a column by adding "AS" (without the quotes) and the new column name (surrounded by double quotes). Make sure the comma is after the new column title.
For example:
SELECT
"ga:date" AS "Month",
"Sum of ga:sessions" AS "Sessions",
"Pageviews per session",
"Bounce rate"
FROM report;
-
Very helpful, thanks! I originally came to this thread thinking it was going to talk about ORDER BY clauses. I've found that if I have a query like this:
SELECT * FROM report ORDER BY 1 DESC, 2 DESC, 3 DESC, 4 DESC
Grow only seems to honor the first clause, even though SQLite's documentation states that multiple columns are supported (http://www.sqlitetutorial.net/sqlite-order-by/). Am I doing it wrong?
-
Hi Gary! That SQL should work with the multiple "order by" clauses. Depending on the data, when you order by several columns it might not make much difference by the time the third or fourth "order by" clause goes into effect. I noticed that you've been working with our support team and they will be able to help out the best if you're still seeing issues.
Please sign in to leave a comment.
Comments
2 comments