> "Matt Magoffin" <postgresql.org@msqr.us> writes:
>> Thanks very much, that helps. Now I'm wondering if it's also possible to
>> then fill in another nested element level in the XML output, from the
>> rows
>> that are aggregated into the <range> count.
>
> Something involving xmlagg in the sub-query, perhaps? No time to
> experiment with it now.
Thanks for the first tip, anyway. I got stuck with trying this out
myself... I had ended up with
select xmlelement(
name "matchback-months",
xmlattributes(1 as "count", 'true' as "multi"),
xmlagg(ranges)) from (
select xmlelement(name "range",
xmlattributes(m.range, count(s.id) as "sales-conv-from-lead"),
xmlagg(sales)) from (
select xmlelement(name "sale",
xmlattributes(ss.vin, ms.lead_id as "lead-id")
) as sales
from mb_sale ss
inner join mb_lead ms on ms.sale_id = ss.id
where
ss.sale_date >= date('2007-08-01') and ss.sale_date <= date('2007-08-30')
and ss.sale_type = 'd'
and ms.range = m.range
order by ss.sale_date
) ssub
) as ranges
from mb_sale s
inner join mb_lead m on m.sale_id = s.id
where
s.sale_date >= date('2007-08-01') and s.sale_date <= date('2007-08-30')
and s.sale_type = 'd'
group by m.range
order by m.range
) sub;
but this does not compile:
ERROR: syntax error at or near "from"
LINE 20: from mb_sale s
^
If anyone has any suggestions, much appreciated.
-- m@
