Group by Two Columns and Display Grand Total in Every Row

Group by two columns and display grand total in every row

Please try:

SELECT
    Code,
    SUM(ItemCount) ItemCount,
    Type,
    SUM(Amount) Amount
FROM
    YourTable
GROUP BY Code, Type
ORDER BY Code

Single grand total ROLLUP with multiple columns

You can control the groupings using grouping sets. If you want the groups that you have plus the total for country and the overall total, then:

SELECT country, state1, city, street, ID, lastname + ', ' + firstname AS Name,
       SUM(salary) AS 'AnnualSalary'
FROM geography1 JOIN
     address
     ON street = streetname JOIN 
     employee ON ID = PID
WHERE termdate IS NULL
GROUP BY GROUPING SETS ( (country, state1, city, street, gender, lastname, firstname), (country), () );

Groupby sum and count on multiple columns in python

It can be done using pivot_table this way:

>>> df1=pd.pivot_table(df, index=['country','month'],values=['revenue','profit','ebit'],aggfunc=np.sum)
>>> df1 
                ebit  profit  revenue
country month                        
Canada  201411     5      10       15
UK      201410     5      10       20
USA     201409     5      12       19

>>> df2=pd.pivot_table(df, index=['country','month'], values='ID',aggfunc=len).rename('count')
>>> df2

country  month 
Canada   201411    1
UK       201410    1
USA      201409    2

>>> pd.concat([df1,df2],axis=1)

                ebit  profit  revenue  count
country month                               
Canada  201411     5      10       15      1
UK      201410     5      10       20      1
USA     201409     5      12       19      2

UPDATE

It can be done in one-line using pivot_table and providing a dict of functions to apply to each column in the aggfunc argument:

pd.pivot_table(
   df,
   index=['country','month'],
   aggfunc={'revenue': np.sum, 'profit': np.sum, 'ebit': np.sum, 'ID': len}
).rename(columns={'ID': 'count'})

                count  ebit  profit  revenue
country month                               
Canada  201411      1     5      10       15
UK      201410      1     5      10       20
USA     201409      2     5      12       19

sum column to show total in every row

It is hard to see what your data looks like -- but from what you posted this is what you want:

SELECT Name, 
       SumColumn,
       SUM(SumColumn) AS TotalColumn
FROM
(
    SELECT Group as Name, SUM(columnA) AS SumColumn
    FROM Table 
    GROUP BY Group
) T

You might want this -- depending on other stuff.

SELECT *,  
       SUM(columnA) OVER (PARTITION BY Group ORDER BY Group) AS SumColumn,
       SUM(columnA) OVER (PARTITION BY Group) AS TotalColumn
FROM TABLE

Append a Grand Total header Row to grouped By data for display purposes

First add soem not exist columns in df_Summay and change order by df_Detail:

df_Summay['Trading_Book'] = 'Grand Totals'

df_Summay = df_Summay.reindex(df_Detail.columns, fill_value='', axis=1)
print(df_Summay)
   Trading_Book Client  Ccy  Total_RFQs  Avg_Competing_Dealers  Notional  \
0  Grand Totals         AUD           7                      8      7000   

   Notional_Done  Hit_Rate  
0         4000.0  0.571429  

And then use this function for append both DataFrames to same excel file:

filename = 'out.xlsx'
append_df_to_excel(filename, df_Summay, sheet_name='Sheet2',  header=None, index=False)
append_df_to_excel(filename, df_Detail, sheet_name='Sheet2', index=False, startrow=1)

Getting Grand Totals using Group By

You are looking for the ROLLUP operator which would add a grand total row at the end of the result set. If you are looking for more complex aggregate totals use ROLLUP or CUBE with the GROUP BY clause, such as the link provided by @MartinSmith or Aggregation WITH ROLLUP

SELECT 
    SUM(dbo.tbl_orderitems.mon_orditems_pprice) AS prodTotal,
    AVG(dbo.tbl_orderitems.mon_orditems_pprice) AS avgPrice,
    count(dbo.tbl_orderitems.uid_orditems_prodid) AS prodQty,
    dbo.tbl_orderitems.txt_orditems_pname
FROM 
    dbo.tbl_orderitems
INNER JOIN 
    dbo.tbl_orders ON (dbo.tbl_orderitems.uid_orditems_orderid = dbo.tbl_orders.uid_orders)
WHERE 
    dbo.tbl_orders.uid_order_webid = <cfqueryparam cfsqltype="cf_sql_integer" value="#session.webid#">
    AND dbo.tbl_orders.txt_order_status = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.sale_status#">
GROUP BY
    dbo.tbl_orderitems.txt_orditems_pname
WITH ROLLUP

MySQL - Group and total, but return all rows in each group

JOIN this subquery with the original table:

SELECT si1.*
FROM sold_items AS si1
JOIN (SELECT member_id
      FROM sold_items
      GROUP BY member_id
      HAVING SUM(amount) > 50) AS si2
ON si1.member_id = si2.member_id

The general rule is that the subquery groups by the same column(s) that it's selecting, and then you join that with the original query using the same columns.

How do I Pandas group-by to get sum?

Use GroupBy.sum:

df.groupby(['Fruit','Name']).sum()

Out[31]: 
               Number
Fruit   Name         
Apples  Bob        16
        Mike        9
        Steve      10
Grapes  Bob        35
        Tom        87
        Tony       15
Oranges Bob        67
        Mike       57
        Tom        15
        Tony        1

To specify the column to sum, use this: df.groupby(['Name', 'Fruit'])['Number'].sum()

---

Related Topics