Group by Value in Column

ewelsh

Hi All, 

 

I'm looking to find a way to group a dataset based on a specific value in a column.

 

Any ideas how to do this?

 

Thanks!

 

Eric

user07652

I don't know if I am doing it right, but using the following calculated field seems to be showing results similar to what I was expecting.  I added this as a calculated field and then graphend the COUNT of this over time.

 

COUNT(DISTINCT `ColumnName`)

zcameron

Yes, that is correct. COUNT is an aggregate function, just like SUM, AVG, MAX, etc. Adding the DISTINCT modifier will ignore duplicates of any values that have already been included in the count. 

 

Well done!

zcameron

Hey Eric,

 

I'm sure we can help you work this out. Where are you hoping to do this grouping (on a card, in a dataflow, etc.)? It sounds like you're hoping to take a dataset and return some values that have been aggregated based on having the same value in a certain column. For example, if your data is like this:

 

id|Series|Value 

1|Alpha|7

2|Alpha|4

3|Alpha|9

4|Beta|3

5|Beta|7

6|Charlie|4

7|Charlie|5

 

 

You'd like to be able to get an output dataset with the Value column aggregated grouped by the Series values like this:

 

Series|Value

Alpha|20

Beta|10

Charlie|9

 

Is that right? If so, you're in luck because there are a lot of good ways to accomplish that. If not, can you clarify so we can know what you're hoping to see?

 

Unknown

@ewelsh, did the above reply help you out?

yadumath

I have the same question. Suggestions on how to do this?

RGranada

Hi,

 

Can you clarify what you need and where you need to do it?

 

Regards,

yadumath

I have a table combining two Google Analytics data sources, which is structured like this:

 

Event Action|Event Category|Evant Lablel|Pageviews|Hostname+Pagepath

A                            B                       C                  1        test1.com+ /shop/page1

D                           E                       F                   1        test1.com+ /shop/page1

G                           H                       I                    1        test1.com+ /shop/page1

J                            K                       L                   2        test2.com+ /shop/page2

M                           N                       O                  2        test2.com+ /shop/page2

 

 test1.com+ /shop/page1 has 1 pageview and 3 events recorded from that 1 pageview. Problem is that pageviews get repeated multiple times for each combination of Action/Category/Label and when i sum pageviews I get an incorrect sum.

 

Desired output:

Event Action|Event Category|Evant Lable||Pageviews|Hostname+Pagepath     | Pageview_correct

A                            B                       C                  1        test1.com+ /shop/page1                1

D                           E                       F                   1        test1.com+ /shop/page1                 

G                           H                       I                    1        test1.com+ /shop/page1

J                            K                       L                   2        test2.com+ /shop/page2                 2

M                           N                       O                  2        test2.com+ /shop/page2

 

I want to group pageviews in such a way that it gives me the first value or average for each Hostname+Pagepath combination. Rest are left blank or set to 0. How do I do this?

 

 

 

user07652

@zcameron,

 

I share this issue as well.  I have data that has multiple rows per "Series" as you've shown.  What I want to often do is graph the count of unique things in that Series over time.  I don't need to aggregate the "Value" as in your example.  I just need to be able to end up with a measure that is the count of unique values in a column so that I can graph that over time.  What's the best way to do that?

user07652

I don't know if I am doing it right, but using the following calculated field seems to be showing results similar to what I was expecting.  I added this as a calculated field and then graphend the COUNT of this over time.

 

COUNT(DISTINCT `ColumnName`)

zcameron

Yes, that is correct. COUNT is an aggregate function, just like SUM, AVG, MAX, etc. Adding the DISTINCT modifier will ignore duplicates of any values that have already been included in the count. 

 

Well done!