Join or sign in Register for your free TechRepublic membership or if you are already a member, sign in using your preferred method below. Use Your Email Use FacebookUse Linkedin

SQL Joins,Subqueries. One Table.

Locked

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

Topic

Clarifications

In reply to SQL Joins,Subqueries. One Table.

Clarifications

Temporary table is a way

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.

Close but no cigar…

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)

Have you tried…

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

A possible way of doing this…

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

Use WITH ROLLUP

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

Rollup w/ Grouping

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

No Rollup funciton available…yet

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

[Author]

Replies