[CS-FSLUG] SQL Query Question

[Don Parris]

Thanks Robert, I'll give this a try tonight or tomorrow.  That looks like a
reasonable solution.

On Mon, Aug 1, 2011 at 22:35, Robert Wohlfarth <rbwohlfarth at gmail.com>wrote:

> 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
>
>
> _______________________________________________
> ChristianSource FSLUG mailing list
> Christiansource at ofb.biz
> http://cs.uninetsolutions.com
>



-- 
D.C. Parris, FMP, LEED AP O+M, ESL Certificate
Minister, Security/FM Coordinator, Free Software Advocate
https://www.xing.com/profile/Don_Parris  |
http://www.linkedin.com/in/dcparris
GPG Key ID: F5E179BE
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://ofb.biz/pipermail/christiansource_ofb.biz/attachments/20110802/8c51a790/attachment.htm>