I think you’re making this a bit more complicated than it needs to be. SELECT ProductID, SUM(IF(PaymentMethod = ‘Cash’, Amount, 0)) AS ‘Cash’, — snip SUM(Amount) AS Total FROM Payments WHERE SaleDate=”2012-02-10″ GROUP BY ProductID
Here is the SQL to create the schema for this example. For anyone who wants to try the solution from @Eric. create table markdetails (studid, subjectid, marks); create table student_info (studid, name); insert into markdetails values(‘A1’, 3, 50); insert into markdetails values(‘A1’, 4, 60); insert into markdetails values(‘A1’, 5, 70); insert into markdetails values(‘B1’, 3, … Read more
For anyone who is still interested in the difference between pivot and pivot_table, there are mainly two differences: pivot_table is a generalization of pivot that can handle duplicate values for one pivoted index/column pair. Specifically, you can give pivot_table a list of aggregation functions using keyword argument aggfunc. The default aggfunc of pivot_table is numpy.mean. … Read more
SELECT CLASS, isnull([AZ],0), isnull([CA],0), isnull([TX],0) FROM #TEMP PIVOT (SUM(DATA) FOR STATE IN ([AZ], [CA], [TX])) AS PVT ORDER BY CLASS
You can group and then unstack. >>> df.groupby([‘year’, ‘month’, ‘item’])[‘value’].sum().unstack(‘item’) item item 1 item 2 year month 2004 1 33 250 2 44 224 3 41 268 4 29 232 5 57 252 6 61 255 7 28 254 8 15 229 9 29 258 10 49 207 11 36 254 12 23 209 Or … Read more
You can pivot your DataFrame after creating: >>> df = pd.DataFrame(data) >>> df.pivot(index=0, columns=1, values=2) # avg DataFrame 1 c1 c2 0 r1 avg11 avg12 r2 avg21 avg22 >>> df.pivot(index=0, columns=1, values=3) # stdev DataFrame 1 c1 c2 0 r1 stdev11 stdev12 r2 stdev21 stdev22
As mentioned by David Anderson Spark provides pivot function since version 1.6. General syntax looks as follows: df .groupBy(grouping_columns) .pivot(pivot_column, [values]) .agg(aggregate_expressions) Usage examples using nycflights13 and csv format: Python: from pyspark.sql.functions import avg flights = (sqlContext .read .format(“csv”) .options(inferSchema=”true”, header=”true”) .load(“flights.csv”) .na.drop()) flights.registerTempTable(“flights”) sqlContext.cacheTable(“flights”) gexprs = (“origin”, “dest”, “carrier”) aggexpr = avg(“arr_delay”) flights.count() ## … Read more
Unfortunately MySQL does not have a PIVOT function which is basically what you are trying to do. So you will need to use an aggregate function with a CASE statement: select pt.partner_name, count(case when pd.product_name=”Product A” THEN 1 END) ProductA, count(case when pd.product_name=”Product B” THEN 1 END) ProductB, count(case when pd.product_name=”Product C” THEN 1 END) … Read more
A PIVOT used to rotate the data from one column into multiple columns. For your example here is a STATIC Pivot meaning you hard code the columns that you want to rotate: create table temp ( id int, teamid int, userid int, elementid int, phaseid int, effort decimal(10, 5) ) insert into temp values (1,1,1,3,5,6.74) … Read more
there are a few ways; using .pivot: >>> origin.pivot(index=’label’, columns=”type”)[‘value’] type a b c label x 1 2 3 y 4 5 6 z 7 8 9 [3 rows x 3 columns] using pivot_table: >>> origin.pivot_table(values=”value”, index=’label’, columns=”type”) value type a b c label x 1 2 3 y 4 5 6 z 7 8 … Read more