SQL Server Performance Forum – Threads Archive
SQL HELP! – multiple counts
Hi all, I’m a developer…… who hasn’t worked on SQL in awhile…. so any help would be welcome. I have 2 tables. CUSTOMER_TABLE—————
customerNumber – number
custName- text
custAddress- text
etc CUSTOMER_LOG
————– customerNumber – number
tranId – char(1) (possible types are "A", "B", "C", "D"….etc, indicating the transaction type the user processed)
The result I want is as follows: customer number | count(*) where tranId = "A" | count (*) where tranId = "B" | …… etc So, I pretty much want the customer number and the cooresponding counts of each transaction they did. How do I get that in a SQL statement? Thanks in advance,
Hasdy
try this: select count(*),tranid from customer_log group by tranid
Do you need CrossTab? Select CusomerNumber, Sum(case when tranid=’A’ then 1 else 0 end) as A_Total,
Sum(case when tranid=’B’ then 1 else 0 end) as B_Total,….from yourTable group by CustomerNumber
Madhivanan Failing to plan is Planning to fail
Yes…. I’ll need it cross since I’ll have to list other information about the customer And Madhivanan…. I won’t be able to do it your way…. since the tranid’s can be added at any time. Has to be done dynamically. ranjitjain, your solution will get the total for all customers…. not the total for EACH customer. Any other ideas?
In SQL 7.0 or SQL 2000, you can either let the client app do this (Access or Excel are capable enough) or you can concatenate the query statement for the different cross-tab values in the same format as Madhivanan suggested (dynamic SQL) and execute that. The problem with dynamic SQL is that the total statement cannot be longer than 8000 characters, a limitation which you may soon run into. In SQL 2005, you can use a PIVOT type query …
What about using stored procedure? You can use a cursor in that loop on the existing distinct values of the tranID and populate a temporary table that has tha maximum number of column abvailable for the TranID values. Then just select the result set from the table odthe non-null tran value. something like: create proc P
AS create table #t (custid int, TranID_1 char(1), TranID_2 char(1)), ……, TranID_N char(1),) –get all distinct value of tranId int a temp table with identity column select identity(int, 1, 1) as val_id ,
val
into #Tr_Val
from (select distinct tranId from CUSTOMER_LOG val) as t declare c1 cursor for
select val_id , val from #Tr_Val declare @val_id int,
@val char(1)
open c1 fetch next from c1 into @val_id, @val while @@fetch_status = 0
begin
declare @s varchar(255) set @s =
‘insert #t (custid, TranID_’ + convert(varchar,@val_id) + ‘)
values (”’ + convert(varchar, @val) + ”’)’ exec (@s) fetch next from c1 into @val_id, @val
end
close c1
deallocate c1 select * from #t –you can dynamicly build your SELECT staement based on the current values in the Tran_IDs columns
Aviel Iluz
Business Intelligence Architect DP Technology
www.dptech.com.au
try this:
declare @t table(custno int,tranid varchar(1))
insert @t values(1,’A’)
insert @t values(1,’B’)
insert @t values(2,’A’)
insert @t values(2,’A’) select custno,tranid+’-‘+convert(varchar(10),count(*)) from @t
group by custno,tranid
Unfortunately, I don’t have control of the actual DB. I’ve used ranjitjains first suggestion… sort of. I’ve obtained a list of customers in a hashtable….. then got the following. select customerNumber, tranid, count(*)
from custtable a right outer join logtable b
on a.customerNumber = b.custNum
group by customerNumber, tranid Then I iterated through this resultset and for each found the cust number in my hashtable… and filled the cooresponding object. not very clean… but it works *shrug*
How about using this Dynamic Cross Tab?
http://weblogs.sqlteam.com/jeffs/archive/2005/05.aspx Madhivanan Failing to plan is Planning to fail
]]>