July 30th, 2013, 06:51 PM
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:
honda civic 1980
acura rsx 2008
honda crv 2010
jeep wrangler 2011
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 06:53 PM.
Reason: edit for clarity
August 5th, 2013, 12:44 PM
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.
August 7th, 2013, 02:37 AM
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
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.
Honda | Civic | 1989
Honda | Civic | 2001
Total | 2
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.