Tuesday, March 7, 2017

Posgresql/Laravel sum and groupBy results

I need to sum all values in table by links_id

CREATE TABLE links_day
(
    id BIGINT DEFAULT nextval('links_day_id_seq'::regclass) PRIMARY KEY NOT NULL,
    links_id BIGINT NOT NULL,
    users_id BIGINT NOT NULL,
    redirect_js_meta BOOLEAN,
    slow_redirect_checker BOOLEAN,
    cost BIGINT,
    name VARCHAR(255) NOT NULL,
    track_link VARCHAR(255) NOT NULL,
    tcl BIGINT,
    ucl BIGINT,
    fcl BIGINT,
    bcl BIGINT,
    act BIGINT,
    eng BIGINT,
    usl BIGINT,
    rsl BIGINT,
    date DATE NOT NULL,
    created_at TIMESTAMP(0),
    updated_at TIMESTAMP(0)
);

I am using Laravel query builder for this:

$links = DB::table('links_day')
            ->select(
                'id',
                DB::raw('sum(tcl) as tcl'),
                DB::raw('sum(ucl) as ucl'),
                DB::raw('sum(fcl) as fcl'),
                DB::raw('sum(bcl) as bcl'),
                DB::raw('sum(act) as act'),
                DB::raw('sum(eng) as eng'),
                DB::raw('sum(usl) as usl'),
                DB::raw('sum(rsl) as rsl'),
                'links_id',
                'users_id',
                'redirect_js_meta',
                'slow_redirect_checker',
                'cost',
                'name',
                'track_link',
                'created_at'
                )
            ->where('users_id', Auth::user()->id)->groupBy('links_id')

I have the error for this: "id must appear in the GROUP BY clause or be used in an aggregate function". Error disappear when adding all non-sum column, but then I don't back right results, I need sum value by links_id. Any helps?



via Laky

Advertisement