[CS-FSLUG] SQL Query Question

[Robert Wohlfarth]

On Mon, Aug 1, 2011 at 5:46 PM, Don Parris <parrisdc at gmail.com> wrote:

> I have a table 'tblcategory' and a table 'tbltransdetails'.  The first
> holds 3 levels of categories, the second holds the details of transactions,
> and stores the category_id from tblcategory for each transaction_item
> record.  I have a nice query that shows all my expenses by lowest-level
> category, but what I want is to show the expenses by the highest-level
> category.
>
> tblcategory (category_id, category_name, parent_id, lineage, deep)
> tbltransdetails (transdetails_id, transaction_id, category_id, item, size,
> quantity, amount, note)
>
> Category Example:
> Transportation: Auto: Fuel (parent(7): child(55): grandchild(60) with a
> lineage of 7-55-60 and depth of "2"
>
> If I do:
> SELECT catgory_name AS "Category", sum(transdetails_amount)
> FROM tblcategory c, tbltransdetails d
> WHERE c.category_id = d.category_id
> GROUP BY category_name;
>
> This gives me the detailed category view, something like: Transportation:
> Auto: Fuel | 150.00
> What I want is to build a query that shows the amounts per *parent*
> category, not the grandchild category.  In other words, I only want to see
> Transportation | 250.00
>

You might take a look at the WITH statement:
http://www.postgresql.org/docs/8.4/static/queries-with.html.

I would try using WITH RECURSIVE to create a table of three columns:
original_category, current_category, parent_category. For example, the WITH
RECURSIVE would create this table:
Transportation | Fuel | NULL
Transportation | Auto | Fuel
Transportation | Transportation | Auto

The SELECT statement JOINS the WITH results on *original_category*.
Something like this maybe...
SELECT
    cats.current_category,
    SUM(tbltransdetails.amount)
FROM
    tbltransdetails
    INNER JOIN cats ON
        cats.original_category = tbltransdetails.category_id
WHERE cats.parent_category IS NULL
GROUP BY cats.current_category

Hope that helps.

-- 
Robert Wohlfarth
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://ofb.biz/pipermail/christiansource_ofb.biz/attachments/20110801/8a59ed5c/attachment.htm>