Question

  • Creator
    Topic
  • #2269599

    SQL Joins,Subqueries. One Table.

    Locked

    by 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.)

All Answers

  • Author
    Replies
    • #2536038

      Clarifications

      by maillists ·

      In reply to SQL Joins,Subqueries. One Table.

      Clarifications

    • #2535999

      Temporary table is a way

      by tony hopkinson ·

      In reply to SQL Joins,Subqueries. One Table.

      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.

      • #2532054

        Close but no cigar…

        by maillists ·

        In reply to Temporary table is a way

        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)

    • #2515715

      Have you tried…

      by djj55 ·

      In reply to SQL Joins,Subqueries. One Table.

      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

      • #2532049

        A possible way of doing this…

        by maillists ·

        In reply to Have you tried…

        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.)

    • #2515688

      Use WITH ROLLUP

      by artful ·

      In reply to SQL Joins,Subqueries. One Table.

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

      SELECT … , 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

      • #2515570

        Rollup w/ Grouping

        by thisisfutile ·

        In reply to Use WITH ROLLUP

        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

      • #2532047

        No Rollup funciton available…yet

        by maillists ·

        In reply to Use WITH ROLLUP

        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.)

Viewing 3 reply threads