Thanks Robert, I'll give this a try tonight or tomorrow. That looks like a reasonable solution.<br><br><div class="gmail_quote">On Mon, Aug 1, 2011 at 22:35, Robert Wohlfarth <span dir="ltr"><<a href="mailto:rbwohlfarth@gmail.com">rbwohlfarth@gmail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;"><div class="im"><div class="gmail_quote">On Mon, Aug 1, 2011 at 5:46 PM, Don Parris <span dir="ltr"><<a href="mailto:parrisdc@gmail.com" target="_blank">parrisdc@gmail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<span style="border-collapse:collapse;color:rgb(51, 51, 51);font-family:arial, sans-serif;font-size:13px"><div>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.</div>
<div><br></div><div>tblcategory (category_id, category_name, parent_id, lineage, deep)</div><div>tbltransdetails (transdetails_id, transaction_id, category_id, item, size, quantity, amount, note)</div><div><br></div><div>
Category Example:</div><div>Transportation: Auto: Fuel (parent(7): child(55): grandchild(60) with a lineage of 7-55-60 and depth of "2"</div><div><br></div><div>If I do:</div><div>SELECT catgory_name AS "Category", sum(transdetails_amount)</div>
<div>FROM tblcategory c, tbltransdetails d</div><div>WHERE c.category_id = d.category_id</div><div>GROUP BY category_name;</div><div><br></div><div>This gives me the detailed category view, something like: Transportation: Auto: Fuel | 150.00</div>
<div>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</div></span></blockquote></div><div><br></div></div>
<div>
You might take a look at the WITH statement: <a href="http://www.postgresql.org/docs/8.4/static/queries-with.html" target="_blank">http://www.postgresql.org/docs/8.4/static/queries-with.html</a>. </div><div><br></div><div>
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:</div>
<div>Transportation | Fuel | NULL</div><div>Transportation | Auto | Fuel</div><div>Transportation | Transportation | Auto</div>
<div><br></div><div>The SELECT statement JOINS the WITH results on <b>original_category</b>. Something like this maybe...</div><div>SELECT </div><div> cats.current_category,</div>
<div> SUM(tbltransdetails.amount)</div><div>FROM </div><div> tbltransdetails </div><div> INNER JOIN cats ON </div><div> cats.original_category = tbltransdetails.category_id</div><div>WHERE cats.parent_category IS NULL</div>
<div>GROUP BY cats.current_category</div><div><br></div><div>Hope that helps.</div><div><br></div>-- <br><font color="#888888">Robert Wohlfarth<br><br>
</font><br>_______________________________________________<br>
ChristianSource FSLUG mailing list<br>
<a href="mailto:Christiansource@ofb.biz">Christiansource@ofb.biz</a><br>
<a href="http://cs.uninetsolutions.com" target="_blank">http://cs.uninetsolutions.com</a><br></blockquote></div><br><br clear="all"><br>-- <br>D.C. Parris, FMP, LEED AP O+M, ESL Certificate<br>Minister, Security/FM Coordinator, Free Software Advocate<div>
<a href="https://www.xing.com/profile/Don_Parris" target="_blank">https://www.xing.com/profile/Don_Parris</a> | <a href="http://www.linkedin.com/in/dcparris" target="_blank">http://www.linkedin.com/in/dcparris</a></div>
<div>GPG Key ID: F5E179BE</div><br>