Sum by category

Moderator: NorbertKrupa

Post Reply
mgbizintel
Newbie
Newbie
Posts: 1
Joined: Mon Oct 05, 2015 9:41 pm

Sum by category

Post by mgbizintel » Mon Oct 05, 2015 9:50 pm

Hello. I have a Vertica table that looks something like this:

Code: Select all

Company       Product         Revenue
A                  Widgets         15
A                  Cat food        5
B                  Sneakers        50
B                  Widgets         12
B                  Cat food        95
B                  Cat food        5
What I am attempting to do is summarize this table so that it looks like this:

Code: Select all

Company       Widgets_rev      Cat_food_rev     Sneakers_rev
A                  15                     5                       (null)
B                  12                     100                    50
I come from a SAS background, and doing something like this is extraordinarily easy with a TRANSPOSE statement. Is there a Vertica SQL equivalent to TRANSPOSE?

My assumption has been to employ the PARTITION/OVER statements, but I've run into a brick wall chasing that particular squirrel.

Thanks in advance!

NorbertKrupa
GURU
GURU
Posts: 527
Joined: Tue Oct 22, 2013 9:36 pm
Location: Chicago, IL
Contact:

Re: Sum by category

Post by NorbertKrupa » Mon Oct 05, 2015 10:39 pm

Search for PIVOT on the forums.
Checkout vertica.tips for more Vertica resources.

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1768
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Sum by category

Post by JimKnicely » Wed Oct 07, 2015 11:07 pm

Like this:

Code: Select all

dbadmin=> select * from test;
 company | product  | revenue
---------+----------+---------
 A       | Cat food |       5
 A       | Widgets  |      15
 B       | Cat food |       5
 B       | Cat food |      95
 B       | Sneakers |      50
 B       | Widgets  |      12
(6 rows)

Code: Select all

dbadmin=> select company "Company", sum(decode(product, 'Widgets', revenue, null)) "Widgets_rev",  sum(decode(product, 'Cat food', revenue, null)) "Cat_food_rev",  sum(decode(product, 'Sneakers', revenue, null)) "Sneakers_rev" from test group by 1;
 Company | Widgets_rev | Cat_food_rev | Sneakers_rev
---------+-------------+--------------+--------------
 A       |          15 |            5 |
 B       |          12 |          100 |           50
(2 rows)
Good news is I hear that built-in PIVOT and UNPIVOT functionality is coming :)
Jim Knicely

Image

Note: I work for HPE. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “New to Vertica SQL”