December 26, 2005 at 1:32 pm
Hi everyone,
Can an SQL guru please help me out here?
I have got 3 tables. here is their schema
Branch
-------
Id Name
1 California
2 Houston
Transactions
--------------
Id Name BranchId
1 TranA 1
2 TranB 2
Items
----------
Id TransactionId Pass
1 1 0
2 1 1
3 2 1
4 2 1
I want a grouping query which gives me records from these three tables. Basically i want some info grouped by various branches. So here is wat my output query should look like -
Branch No of Transactions No of Items ItemsPass Itemsfail
------ ------------------ ----------- --------- ---------
California 1 2 1 1
Houston 1 2 2 0
I am able to group them and get their Transaction total, item total. It is the last 2 columns which are proving to be a real pain. I think i might have to use left joins or something along those lines. here is wat i have come up with till now.
Select B.Name,
count(distinct T.Id) as 'Tran Count',
count(I.Id) as 'Item Count'
from Transactions T,
Branches B,
Items I
where T.BranchId = B.Id and
T.Id = I.TransactionId
group by B.BranchName
Can anyone help me get the last 2 counts?
Thanks in advance,
Shahed
December 26, 2005 at 11:39 pm
Select Name,TranCount,ItemCount,Sum(Pass) as Pass,Sum(Fail) as Fail
from (
Select B.Name,
count(distinct T.Id) as 'TranCount',
count(I.Id) as 'ItemCount', case when I.P=1 then count(I.P) else 0 end 'Pass',
case when I.P=0 then count(I.P) else 0 end 'Fail'
from #T T inner join
#B B on T.BId = B.Id
inner join
#I I
on T.Id = I.TId
group by B.Name,I.P
) SubTable
group by Name,TranCount,ItemCount
Its Over.....
So, now think over my thread 'selecting a effective date from a table '
Ragards Naveed Ahamd
December 27, 2005 at 2:17 am
Thanks Naveed,
I got the same result byu doing this
select B.BranchName,
count(distinct T.Id) as 'Tran Count',
count(I.Id) as 'Item Count',
sum(case when cast(I.iqaresult as int)+ cast(I.iuaresult as int) =0 then 1 else 0 end) as PassCount,
sum(case when cast(I.iqaresult as int)+ cast(I.iuaresult as int) >0 then 1 else 0 end) as FailCount
from rc_Branches B,
rc_Items I,
rc_Transactions T
where T.BranchId = B.Id and
T.Id = I.TransactionId
group by B.BranchName
I went through ur thread, but am not quite sure wat you are trying to do? Can you elaborate a bit more?
December 27, 2005 at 2:42 am
Select branchname,count(distinct(transactionid)),count(distinct(id)),
sum(pass) as pass,sum(fail) as fail
from (
select b.name as branchname, t.name as tranname,
i.transactionid, i.id,
case pass when 1 then 1 else 0 end as pass,
case pass when 0 then 1 else 0 end as fail
From items i inner join
(transactions t inner join branch b on t.branchid = b.id)
on i.transactionid = t.id
) as X
group by branchname order by branchname
January 3, 2006 at 6:31 am
Assuming that Items.Pass is always 1 for passed and 0 for failed (if not, use CASE inside the sums):
select B.Name,
count(distinct T.Id) as 'Tran Count',
count(I.Id) as 'Item Count',
sum(I.Pass) as ItemsPass,
sum(1 - I.Pass) as ItemsFail
from Transactions T join Branch B on T.BranchId = B.Id
join Items I on T.Id = I.TransactionId
group by B.Name
Regards,
Ronald
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply