1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Rep Power

    Last row total of column

    I cant find the answer to his anywhere but is there a way to have a query be something like:

    select make,model,stockyear
    from stock


    honda civic 1980
    acura rsx 2008
    honda crv 2010
    jeep wrangler 2011
    total 4

    i want a total row at the bottom that I can have either a count or an avg depending on what the column is for.

    I have tried subqueries and I can get it where it gives me another column with a count but not a new row at the bottom. I have read about rollup but it doesnt look like postgres supports rollup

    Anyone have any ideas that could help me? Thanks a lot

    EDIT: sorry the total 4 would be a total count on one of the columns it most likely doesnt matter what column but I would probably run a count distinct on a stock number
    Last edited by mainske; July 30th, 2013 at 05:53 PM. Reason: edit for clarity
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2010
    Rep Power
    The way I've done this in the past is either:

    Do two separate queries
    - Pros: gets the output just as you want
    - Cons: requires two queries

    Do a union of your details query and a total query
    - Pros: One query only
    - Cons: There's no way to for the "total" row to the end of the output, unless you do something ugly like calling it "zzzTotal" or similar.

    Do it programmatically on the client side.
    - Pros: You get exactly what you want
    - Cons: You have to involve some sort of client side code in some language or another.

    The best option depends on the context of what you're trying to accomplish.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Usually Japan when not on contract
    Rep Power
    For a complete answer you need to give a more detailed explanation of the kind of output you want. That is, you say "...a total row at the bottom that I can have either a count or an avg depending on what the column is for" without really explaining what you mean.

    But in general you can get the behavior you want (depending on exactly what that is) with CTEs ("WITH foo AS..."), window functions, or a combination where you UNION the result of a CTE with whatever windowed output you want to another CTE that provides the base data.

    Remember, though, to add rows the columns have to match up, because you are essentially adding items to a list of fixed-form tuples. So you can get:
    Item  | Honda | Civic | 1989
    Item  | Honda | Civic | 2001
    Count | Honda | Civic | 2
    But not
    Honda | Civic | 1989
    Honda | Civic | 2001
    Total | 2
    Because each tuple, whether a rendered result or an actual "table" stored on disk somewhere, is a complex data type, and the last row there "total" doesn't match the type of the other tuples, so it can't be part of the returned set.

    But this doesn't have be a bad thing. Consider the first row in the first example that you can do. That first column can be anything you want, so you could do averages, counts, whatever and label them as such, so long as you also label the rows that represent an individual item. Its even better if you add a column for your aggregate/window results -- I mean, yeah a "year" is usually an integer and so might your aggregate answers, but a year certainly doesn't mean the same thing as an aggregate answer. "Honda" and "Civic" always mean the same thing, though, so you can usefully use these answers either within the database in further processing or within an application later on without getting your wires crossed.

IMN logo majestic logo threadwatch logo seochat tools logo