[EMAIL PROTECTED] wrote:
> I have two tables I have to join. Table1 contains tasks, Table2 is a group table
>that contains tasks that are grouped from the Table1. Table one as a field called
>group_id which houses the coresponding Task_id from Table2 as the foreign key. Table1
>tasks contain all the accounting information. When you call Table2 to get the data
>for the group you mathmatically sum up the amounts fields. I also need to get a count
>of the total of groups not the total of the tasks within the group.
>
> Problem: I get the sql to return correctly except my count is based on the total
>number of tasks. So if I had 3 groups of 5 tasks each I want to return count of 3,
>instead I get of 15.
If I understand you correctly:
SELECT rf1, COUNT(*) as rf0, rf2, OLDEST, NEWEST
FROM (
SELECT t.status as rf1,ROUND(SUM(th.current_amt),3) as
rf2,DATEDIFF(dd,min(t.start_dte),getdate()) as
OLDEST,DATEDIFF(dd,max(t.start_dte),getdate()) as NEWEST
FROM task_group t,task_header th
WHERE t.task_id = th.group_id
AND th.owner ='B62994'
AND t.owner = th.owner
AND (t.task_type ='ALL'
OR t.task_type = 'DB'
OR t.task_type = 'DD'
OR t.task_type = 'OAC'
OR t.task_type = 'PDI'
OR t.task_type = 'UCD')
AND t.status not in ('Closed','CR')
AND (t.status <> '' or t.status <> ' ')
GROUP BY t.status
) AS sub
BTW, the following:
AND (t.status <> '' or t.status <> ' ')
is much easier written as :
AND t.status IS NOT NULL
Jochem
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community. http://www.fusionauthority.com/ads.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
