CODEWITHSUNDEEP
phpmysqlgroup-by
battery bob
battery bob

Reputation: 11

Group by quarters annual

I need to group by quarter year by year in my .php file. In bold type I show the code that needs to be fixed aka switch case 4. Just now I group correct for year, month, week and day, but now I need quarter name case 4. I tried this code for GROUP FUNCTION, but is shows me 5 groups in 2010 and 2011 and this is wrong.

switch ($level) 
{

  case '4':**
    $group = "FLOOR(EXTRACT(YEAR_MONTH FROM FROM_UNIXTIME(invoice_date))/3)";</b>
    break;
}

FIXED IS


SQL PART


case '4':
                    $group = "EXTRACT(YEAR FROM FROM_UNIXTIME(invoice_date)) as group_field2, EXTRACT(QUARTER FROM FROM_UNIXTIME(invoice_date))";
                    break;

    $sql_select = $db->query("SELECT sum(amount) AS invoice_amount, " . $group . " AS group_field FROM 
                " . DB_PREFIX . "invoices WHERE invoice_date>= " . $startup . " " . $user_query . " GROUP BY group_field, group_field2 ORDER BY group_field2 DESC, group_field DESC");


TEXT - LINK PART

                    case '4':
                        $start = strtotime('+' . (($view_details_received['group_field'] - 1) * 3) . ' months', strtotime($view_details_received['group_field2'] . '/1/1'));
                        $finish = strtotime('+' . (($view_details_received['group_field'] - 1) * 3 + 3) . ' months', strtotime($view_details_received['group_field2'] . '/1/1'));
                        $start = getdate($start);
                        $finish = getdate($finish - 86400);
                        $title = 'Q' . $view_details_received['group_field2'] . ', ' . $view_details_received['group_field'];
                        break;

Upvotes: 1

Views: 363

Answers (1)

Kamil Šrot
Kamil Šrot

Reputation: 2221

You can use EXTRACT(QUARTER FROM date) in your SQL and then do regular GROUP BY

See EXTRACT documentation on MySQL webpage and table of possible arguments to it.

Upvotes: 2

Related Questions