August 24, 2012 at 6:17 pm
having a reporting situation.
create table CustTable (Customer varchar(10),Location varchar(10))
insert into CustTable select 'IBM','US' union all
select 'DELL','US' union all
select 'Sony','JAPAN'
create table OrderTable (OrderNo int, Cust varchar(5))
insert into OrderTable select 1,'IBM' union all
select 2,'IBM' union all
select 3,'IBM' union all
select 4,'HP' union all
select 5,'SONY'
Tryin to generate a report something like below.
IBM - 3
Dell - 0
Sony -1
Hp - 1
select cust, count(OrderNo) as OrderTotal from CustTable A full join OrderTable B on A.Customer =B.Cust
group by Cust --any thoughts
August 24, 2012 at 9:47 pm
Try this:
;WITH AllCustomersCTE AS (
SELECT Customer FROM CustTable
UNION
SELECT DISTINCT Cust FROM OrderTable
)
SELECT
A.Customer
,COUNT(B.OrderNo) AS OrderTotal
FROM AllCustomersCTE AS A
LEFT JOIN OrderTable AS B
ON A.Customer = B.Cust
GROUP BY A.Customer
--Vadim R.
August 25, 2012 at 12:23 am
what kind of relationship , do you have in the tables ?
select customer , count(cust)from CustTable a
left join OrderTable
on customer=Cust
group by customer
this may have been the proper code;
why is that the customer and order table have exclusive records ?
~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:
August 25, 2012 at 12:46 am
demonfox (8/25/2012)
what kind of relationship , do you have in the tables ?
select customer , count(cust)from CustTable a
left join OrderTable
on customer=Cust
group by customer
this may have been the proper code;
why is that the customer and order table have exclusive records ?
Yes, this is first thing that comes to mind. But looks like there are no enforced relationships and therefore orphaned records in the OrderTable.
--Vadim R.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply