Questions

SQL Joins,Subqueries. One Table.

+
0 Votes
Locked

SQL Joins,Subqueries. One Table.

maillists
First, I am NOT an sql expert or anywhere close to that. I'm attempting to put the work portions on the Database backend as there is less cpu throttling done there than on a cgi frontend, here's what I need to accomplish:

Have a table with something like this for columns:

uid, ownerid, name, field1, field2, datefield, field3, field4, field5, field6, field7 field8, moneyfield.


What I would like to accomplish:


A select that grabs all the fields in all the rows, (except uid), and groups by ownerid, then adds a row that sums the moneyfields for each ownerid.

I have tried things such as:

SELECT *, sum(moneyfield)as total from table, order by ownerid.

Of course this brings up errors. I have tried being more explicit and naming my fields instead of using * (since I want to drop the uid in my resultset..) but even doing something like

SELECT field, field, field,..., sum(moneyfield) as total from table order by ownerid, otherfields...; I get errors...and when I get rid of all syntax and omitted field errors.. I get JUST a sum line. I don't want a sum line, and don't understand a thing about JOINS, UNIONS, etc...despite having investigated some tutorials..which just made me more confused.

Any suggestions?

(I can handle this issue in Perl, but again, I'm running up against cpu throttling issues in the CGI side of things that aren't in the DB backend.)
  • +
    0 Votes
    Tony Hopkinson

    Have a table with something like this for columns:

    Create Table #temp(int sortorder, name ... ,currency total)

    insert into #temp
    Select distinct 0, ownerid, name, field1, field2, datefield, field3, field4, field5, field6, field7 field8, null from mytable

    insert into #temp(SortOrder,ownerid,total)
    select 1,ownerid, sum(moneyfield) groupby ownerid,sortorder from mytable

    select * from #temp order by ownerid,sortorder

    or some such.

    +
    0 Votes
    maillists

    This comes back with the original issue of
    the Total row not necessarily being at the
    bottom of the group of records UNLESS i am
    inserting with a uid, grouping by ownerid,
    then sorting in the group by uid. This
    doesn't really work, because the grouping
    prevents a sort order, and sorting by
    ownerid then uid doesn't work either. I
    think I will need a relational solution for
    this..especially since my DB is lacking a
    ROLLUP function. (As of now postgres 8.2,
    at least on a Win32 platform, does not
    understand rollup or cube)

    +
    0 Votes
    djj55

    SELECT ownerid, name, field1, field2, datefield, field3, field4, field5, field6, field7 field8, sum(moneyfield)as total
    from table
    group by ownerid, name, field1, field2, datefield, field3, field4, field5, field6, field7 field8
    order by ownerid

    Note I left out the uid and moneyfield in the list of columns.
    djj

    +
    0 Votes
    maillists

    But I suspect the client will want the
    Money field for each individual item as
    well as the totals..

    Therein lies the problem. Especially since
    when i do a prepared statement I have no
    control over the order the original data
    goes in, so I can't assure that the "TOTAL"
    line will go in AFTER another line, and I
    can't be sure that there won't be additions
    to the ownerid later. But this may be the
    next best thing in the report. I suspect
    I'll have to handle it in Perl...or some
    other language...it's just easier to code
    it in perl..

    Especially since PG lacks a rollup and a
    cube function..(well it has a cube function
    which has nothing to do with the SQL
    defined CUBE funciton.)

    +
    0 Votes
    artful

    There is a GROUP BY extension that does exactly what you want.

    SELECT ... <columns of interest>, SUM(moneycolumn)
    GROUP BY OwnerID WITH ROLLUP

    You will get a sum row for each OwnerID and an additional row that sums everything.

    hth,
    Arthur

    +
    0 Votes
    thisisfutile

    SQL Books online uses this code that is a nice touch. It involves the GROUPING command (usable with ROLLUP) which helps distinguish between the two types of Null values that can appear with ROLLUP:

    (Note, I didn't change any of the references to match this thread...this is copy/pasted from the books online)

    SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'
    ELSE ISNULL(Item, 'UNKNOWN')
    END AS Item,
    CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'
    ELSE ISNULL(Color, 'UNKNOWN')
    END AS Color,
    SUM(Quantity) AS QtySum
    FROM temp_Inventory
    GROUP BY Item, Color WITH ROLLUP


    Here's a Create/Insert for some sample data to see this query in action...

    CREATE TABLE temp_Inventory(item varchar(10), color varchar(10), quantity int)

    GO

    insert temp_Inventory
    select 'Table', 'Blue', 124
    union all
    select 'Table', 'Red', 223
    union all
    select 'Chair', 'Blue', 101
    union all
    select 'Chair', 'Red', 210

    GO

    select *
    from temp_Inventory

    +
    0 Votes
    maillists

    Postgres doesn't support that feature
    yet.Grr...

    (And it probably won't as it has a CUBE
    function which has nothing to do with SQL
    cubing, and more to do with literal cubes.)

  • +
    0 Votes
    Tony Hopkinson

    Have a table with something like this for columns:

    Create Table #temp(int sortorder, name ... ,currency total)

    insert into #temp
    Select distinct 0, ownerid, name, field1, field2, datefield, field3, field4, field5, field6, field7 field8, null from mytable

    insert into #temp(SortOrder,ownerid,total)
    select 1,ownerid, sum(moneyfield) groupby ownerid,sortorder from mytable

    select * from #temp order by ownerid,sortorder

    or some such.

    +
    0 Votes
    maillists

    This comes back with the original issue of
    the Total row not necessarily being at the
    bottom of the group of records UNLESS i am
    inserting with a uid, grouping by ownerid,
    then sorting in the group by uid. This
    doesn't really work, because the grouping
    prevents a sort order, and sorting by
    ownerid then uid doesn't work either. I
    think I will need a relational solution for
    this..especially since my DB is lacking a
    ROLLUP function. (As of now postgres 8.2,
    at least on a Win32 platform, does not
    understand rollup or cube)

    +
    0 Votes
    djj55

    SELECT ownerid, name, field1, field2, datefield, field3, field4, field5, field6, field7 field8, sum(moneyfield)as total
    from table
    group by ownerid, name, field1, field2, datefield, field3, field4, field5, field6, field7 field8
    order by ownerid

    Note I left out the uid and moneyfield in the list of columns.
    djj

    +
    0 Votes
    maillists

    But I suspect the client will want the
    Money field for each individual item as
    well as the totals..

    Therein lies the problem. Especially since
    when i do a prepared statement I have no
    control over the order the original data
    goes in, so I can't assure that the "TOTAL"
    line will go in AFTER another line, and I
    can't be sure that there won't be additions
    to the ownerid later. But this may be the
    next best thing in the report. I suspect
    I'll have to handle it in Perl...or some
    other language...it's just easier to code
    it in perl..

    Especially since PG lacks a rollup and a
    cube function..(well it has a cube function
    which has nothing to do with the SQL
    defined CUBE funciton.)

    +
    0 Votes
    artful

    There is a GROUP BY extension that does exactly what you want.

    SELECT ... <columns of interest>, SUM(moneycolumn)
    GROUP BY OwnerID WITH ROLLUP

    You will get a sum row for each OwnerID and an additional row that sums everything.

    hth,
    Arthur

    +
    0 Votes
    thisisfutile

    SQL Books online uses this code that is a nice touch. It involves the GROUPING command (usable with ROLLUP) which helps distinguish between the two types of Null values that can appear with ROLLUP:

    (Note, I didn't change any of the references to match this thread...this is copy/pasted from the books online)

    SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'
    ELSE ISNULL(Item, 'UNKNOWN')
    END AS Item,
    CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'
    ELSE ISNULL(Color, 'UNKNOWN')
    END AS Color,
    SUM(Quantity) AS QtySum
    FROM temp_Inventory
    GROUP BY Item, Color WITH ROLLUP


    Here's a Create/Insert for some sample data to see this query in action...

    CREATE TABLE temp_Inventory(item varchar(10), color varchar(10), quantity int)

    GO

    insert temp_Inventory
    select 'Table', 'Blue', 124
    union all
    select 'Table', 'Red', 223
    union all
    select 'Chair', 'Blue', 101
    union all
    select 'Chair', 'Red', 210

    GO

    select *
    from temp_Inventory

    +
    0 Votes
    maillists

    Postgres doesn't support that feature
    yet.Grr...

    (And it probably won't as it has a CUBE
    function which has nothing to do with SQL
    cubing, and more to do with literal cubes.)