Add Mysql cumulative frequency field using any method

Question

i have a sales 'table' and from it i have created a 'view' called dailyactivity which gives me the total sales made on each day. i am trying to add a cumulative frequency field but it returns an empty coloumn.can any one point me in the right direction.This is what i Have enter image description here

This is what i would like enter image description here

Does this really belong in your database layer? Why don't you calculate the cumulative sum in the presentation layer of your application? — eggyal, May 01 '13 at 10:16
@ eggyal Am using a php application generator and it only generates lists and reports based on views and tables — Law, May 01 '13 at 10:19

score 0 · Accepted Answer · answered May 01 '13 at 11:11

0

Query:

SELECT
d1.Date_TR,
sum(d1.Sales) AS Daily_Sales,
(SELECT SUM(d2.Sales)
 FROM dailyactivity d2
 WHERE d2.Date_TR <= d1.Date_TR) AS cumulative_sum
FROM dailyactivity d1
GROUP BY d1.Date_TR
ORDER BY d1.Date_TR ASC

answered May 01 '13 at 11:11

Justin

9,634
6
35
47

i would still like to know why it Wouldn't work with the parameter. i am learning to write complex queries.this would help in learning – Law May 01 '13 at 11:26
I don't know... should work and your query, try here with your data http://sqlfiddle.com/#!2/8645d/16 if will not work then something wrong maybe with your tool... – Justin May 01 '13 at 11:41

Rayman Preet · Answer 2 · 2019-12-03T19:59:58.930

This script will create the required cumulative distribution function (CDF) query for any table:

    #!/bin/bash

tableName=$1
cdfParam=$2
otherParams=$3

usage(){
    echo "Usage: $0 <Table name> <Column/Columns for CDF> [Additional parameters to output]"
    echo ""
    echo "Get CDF of the Age column from the Persons table and print the Name and Gender of each person"
    echo "e.g., $0 Persons Age Name,Gender"
    echo ""
    echo "Get CDF of the difference between Salary & profil from the Persons table and print the Name and Gender of each person"
    echo "e.g., $0 Persons 'Salary-Profit' Name,Gender"
    echo ""
    echo "Get CDF of Age from a derived table called employeeTable"
    echo "e.g., $0 '(select * from Employees) as employeeTable' Age"
    exit 1
}

[[ $# -lt 2 ]] && usage

if [ -z "$otherParams" ]
then
    echo "SELECT $cdfParam as cdf_paramValue, (SELECT count(*) FROM $tableName WHERE $cdfParam <= cdf_paramValue) AS cumulative_sum FROM $tableName ORDER BY ($cdfParam) desc";
else
    echo "SELECT $otherParams,$cdfParam as cdf_paramValue, (SELECT count(*) FROM $tableName WHERE $cdfParam <= cdf_paramValue) AS cumulative_sum FROM $tableName ORDER BY ($cdfParam) desc";
fi

Add Mysql cumulative frequency field using any method

2 Answers2