Get sum of data for latest year

201
April 28, 2017, at 8:28 PM

I have a database in which data is inserting yearly basis so I want to sum up data by industry for the latest yearly added as of sometimes happens as in database the data of some companies are added in 2015 some for 2016 so the problem here is I want to get data as sum value of most latest data added

 $sql           = "SELECT id FROM companies WHERE category = '".$company_info["category"]."'";
    $total_comanies = $this->basic_model->getCustomRows($sql); // This is a custom made model which gets data in return as array
    $total_count    = count($total_comanies);
    $companies = array();
    for($i = 0; $i <= $total_count; $i++) {
        if(!empty($total_comanies[$i]['id'])) {
            $companies[] = $total_comanies[$i]['id'];
        }
    }
    $ids = join(', ', $companies);
    $sql1 = "SELECT SUM(number_of_shares_issued) FROM companies_annual_data WHERE company_id IN ($ids) ORDER BY year DESC";

sample data

+---Company_id----+----Year-----+----number_of_shares_issued-----+
    +     20          +    2011     +     425                        +
    +     21          +    2011     +     425                        +
    +     22          +    2011     +     425                        +
    +     23          +    2011     +     425                        +
    +     21          +    2012     +     425                        +
    +     20          +    2012     +     425                        +
    +     23          +    2012     +     425                        +
    +     20          +    2013     +     425                        +

So it should be sum(data of id 20 year 2013, data of id 21 year 2012, data of id 21 year 2011) and so on you know what i wanted to do now

Answer 1

if u have insertion date column in your table ,use insertion date to get the latest data

READ ALSO
Locate, Substring query

Locate, Substring query

I am trying to pull the data from between 2 string points in mysql, my example script would be

296
mysql: How to exclude rows from table which exist in table_alias with good perfomanse?

mysql: How to exclude rows from table which exist in table_alias with good perfomanse?

I've sql with NOT EXIST and it works very slowly in big db:

369
Calculating difference of two values and sorting based on the result with mysql

Calculating difference of two values and sorting based on the result with mysql

I want to calculate bonus of the current date minus the bonus of the first day of the current month for every user of a given group

307
Non NULL datetime values from a SELECT statement without table

Non NULL datetime values from a SELECT statement without table

I have a small bash function that works perfectly well designed to check a date's validity abusing MySQL because it is way faster than using GNU dateThe aim is to return a validated date in the format YYYYmmdd to be used as input to other commands

298