logo
down
shadow

PostgreSQL Group values by category, count and calculate percentage


PostgreSQL Group values by category, count and calculate percentage

By : Neluțu Nicoară
Date : November 20 2020, 03:01 PM
it should still fix some issue Consider creating your category column conditionally with CASE statement and include it as a GROUP BY in the derived table aggregate query
code :
select *,
    (error::float / total::float * 100) as error_percentage,
    (zero::float / total::float * 100) as zero_percentage,
    (win::float / total::float * 100) as win_percentage
    from (
        select
            case substring(tag::text, 1, 1)
                 when '1' then 'Attack' 
                 when '2' then 'Block' 
                 when '3' then 'Dig' 
                 when '4' then 'Reception' 
                 when '5' then 'Service' 
                 when '6' then 'Setting' 
            end as category,
            count(*) filter (where tag - round(tag/100, 0)*100 = 0) as error,
            count(*) filter (where tag - round(tag/100, 0)*100 = 10) as zero,
            count(*) filter (where tag - round(tag/100, 0)*100 = 20) as win,
            count(*) filter (where tag - round(tag/100, 0)*100 <= 20) as total
        from moves
        where match_uuid = 'd7eea231-a63d-4d73-b48f-5ca8541ec9cf' and set = 1
        group by
            case substring(tag::text, 1, 1)
                 when '1' then 'Attack' 
                 when '2' then 'Block' 
                 when '3' then 'Dig' 
                 when '4' then 'Reception' 
                 when '5' then 'Service' 
                 when '6' then 'Setting' 
            end
    )
as attack_stats


Share : facebook icon twitter icon
SSRS 2016 - How to calculate a percentage difference between two values in a group?

SSRS 2016 - How to calculate a percentage difference between two values in a group?


By : user141
Date : March 29 2020, 07:55 AM
should help you out It looks like your scope is incorrect. Check what the rowgroup is called where you group by item id (let's say the row group is called "yourItemRowGroupName").
Then change you expression to use that scope rather than "Date1".
code :
=(Last(Fields!total_sales1.Value)- First(Fields!total_sales1.Value)) / First(Fields!total_sales1.Value) * 100
=(Last(Fields!total_sales1.Value, "yourItemRowGroupName")- First(Fields!total_sales1.Value, "yourItemRowGroupName")) / First(Fields!total_sales1.Value, "yourItemRowGroupName") * 100
How to calculate percentage by count of column values

How to calculate percentage by count of column values


By : user2735806
Date : March 29 2020, 07:55 AM
I hope this helps . Given table like this. , I would use conditional aggregation:
code :
select class, avg( tag like 'a%' ) as a_ratio
from t_test
group by class;
Pandas Groupby - Calculate percentage of values per group total value

Pandas Groupby - Calculate percentage of values per group total value


By : user3102019
Date : March 29 2020, 07:55 AM
This might help you I have this Pandas group by statement: , Reproducing the dataset like you have:
code :
df = pd.DataFrame()
df['outcome'] = ['win', 'win', 'win', 'loss', 'loss', 'loss']
df['teams'] = ['manu', 'chelsea', 'arsenal', 'manu', 'chelsea', 'arsenal']
df['points'] = [120, 75, 10, 30, 75, 150]
grouped = df.groupby(['outcome', 'teams'])['points'].sum()
                 points
outcome teams          
loss    arsenal     150
        chelsea      75
        manu         30
win     arsenal      10
        chelsea      75
        manu        120
grouped / grouped.groupby(level = 1).sum()
outcome teams    points     
loss    arsenal  0.9375
        chelsea  0.5000
        manu     0.2000
win     arsenal  0.0625
        chelsea  0.5000
        manu     0.8000
How to calculate the percentage of values group by date in power bi

How to calculate the percentage of values group by date in power bi


By : user3127017
Date : March 29 2020, 07:55 AM
this one helps. It would be similar to this:
The total number of statuses measure:
code :
status_total = 
    VAR passed = COUNT(Table_1[status])
RETURN IF( ISBLANK(passed), 0, passed)
passed = 
    VAR passed = CALCULATE(
                    COUNTROWS(Table_1),
                    FILTER(Table_1, Table_1[Status] = "PASS")
    )
RETURN IF( ISBLANK(passed), 0, passed)
failed = 
    VAR failed = CALCULATE(
                     COUNTROWS(Table_1),
                     FILTER(Table_1, Table_1[Status] = "FAIL")
    )
RETURN IF( ISBLANK(failed), 0, failed )
passed % = DIVIDE([passed], [status_total],0)
failed % = DIVIDE([failed], [status_total],0)
Calculate percentage of each category in each group in R

Calculate percentage of each category in each group in R


By : Amit Pal
Date : March 29 2020, 07:55 AM
like below fixes the issue I have a data frame like this , You can use dplyr package
Related Posts Related Posts :
  • How to create table via ODBC in ansi sql compliant way?
  • Postgres. How to get all the parents that meet the childs criteria?
  • 'Exploratory' SQL queries that uses one criteria to find more criteria
  • Adding Date columns
  • Display ContactName from Guid in Gridview using SQLDataSource / Asp.Net
  • making groups based on match between column 1 and column 2 from 1 table
  • Teradata 15.10 - Remove set of characters from a string
  • PostgreSQL array_agg(INTEGER[])
  • Does a query goes through all data when you only select the last N?
  • Displaying results based on customer first order after specific date SQL Server
  • Sum records and add note what was summed up in sql
  • Need help in generating values
  • RODBC - import a table
  • SQL: How to Group By task and choose only those that are completed?
  • PostgreSQL/PostGIS Count number of visits
  • How to I get a total count?
  • Many-to-many SQL relationship
  • Procedure call inside procedure
  • How to have decreasing running total in Oracle sql
  • SQL Group function query?
  • Which is the best way to calculate Year/Month in SQL Server?
  • Best way of sanitize unparametrizable sql
  • Querying for JSON data in Oracle creates syntax error
  • Show top 1 by max column
  • SQL: Insert newly created column in the same table
  • How to extract numbers after string using regexp?
  • SQL Query - Group consecutive items based on condition
  • Users who work in same department
  • Syntax error near column value Vb
  • Oracle Trigger BEFORE INSERT has No data found
  • What kind of join to use on SQL tables
  • Is there a way to add a constant value dynamically to all records returned in Hive?
  • SQL optimization (inner join or selects)
  • EF 6.x, LINQ-to-SQL and raw SQL clauses
  • Simple SQL Variable Assignment Only Returns One Letter: Why?
  • Converting a custom timestamp to date
  • SQL Server : inserting Player vs Player names in to new table from tblEntrants
  • invalid identifier in sql
  • PL/SQL - I keep getting this error when concatenating: PLS-00306: wrong number or types of arguments in call to '||'
  • Count records only from left side of a LEFT JOIN
  • get everything before a string including itself oracle
  • Format Data from Word Doc to SQL using RegEX
  • Conditional formatting on MAX value row
  • MS-Access : selecting data from two tables and only returning you need
  • SQL Server: optimal indexing strategies for many-to-many join
  • DBgrid column very wide
  • MS Access SQL - Most Recent Record for Each Consultant ID
  • Update table: Summary of previous rows without using cursor or while loop
  • PostgreSQL: built-in function to remove substring starting with certain pattern
  • ORA-00909: invalid number of arguments
  • How to summarize all possible combinations of variables?
  • Select Column within a Column SQL
  • PostgreSQL Inserting 2 relationships at once
  • T sql - How to store results from a dynamic query using EXEC or EXECUTE sp_executesql
  • How do I parse my json into CSV using regex?
  • Reverse foreign key cascading (or how to collect database garbage)
  • SQL Pivot Questions
  • Insert records into a table with a condition in SQL Server 2016
  • display null value using rank functions in oracle sql
  • SQL - Get count of group by column but also select top item of group
  • shadow
    Privacy Policy - Terms - Contact Us © voile276.org