split data using two conditionals into 2 results columns
-
hi
im using sql server 2005
i have a table called download_transactions which contains wallpaper and video downloads. videos have a download_type of 110 and wallpapers 104. i need to pull out a count of these transactions where download_type is 110 and 104.
so one results column is count of 110 downloads
and the other column is count of 104.
thing is, one canned do; ..
where download_type 110 And 104
where download_type in (110, 104)
because this just confuses SQLserver. two conditionals.
how to do this, any ideas.
must i use a cursor to go through results once for 110, then again for 104?
at the moment, i select to pull out the count, and it gives two rows for each download type e.g.
clientID date count
50015 2007-03-06 00:00:00.000 922
50015 2007-03-06 00:00:00.000 1430
50020 2007-03-06 00:00:00.000 486
50020 2007-03-06 00:00:00.000 872
two rows for each clientID, one is count of 110, and one is count of 104.
i just need the 2nd row count to be in a 4th column next, like this:
clientID date 104/count 110/count
50015 2007-03-06 00:00:00.000 922 1430
this is the sql i am using:
[brown]declare @start_date datetime
declare @end_date datetime
declare @start_timeshift int
declare @end_timeshift int
declare @client_id int
select @start_timeshift =0,
@end_timeshift =23,
@start_date = (dateadd(hh,@start_timeshift,'2007-03-06')),
@end_date = (dateadd(hh,@end_timeshift,'2007-03-06'))
select c.client_id,
@start_date [date],
count(txn.download_transaction_id) + '' + count(txn.download_transaction_id)
from [main_tbl].dbo.download_transactions txn with ( nolock )
inner join [main_tbl].dbo.billing_partners as b with ( nolock ) on ( ( b.billing_partner_id = txn.billing_partner_id )
and ( b.is_active = 1 ) and ( b.is_development <> 1 ) )
inner join [main_tbl].dbo.suppliers s with ( nolock ) on ( ( s.supplier_id = txn.supplier_id )
and ( s.is_active = 1 ) and ( s.is_development <> 1 ) )
inner join [main_tbl].dbo.clients c with ( nolock ) on ( ( c.client_id = txn.client_id )
and ( c.is_active = 1 ) and ( c.is_development <> 1 ) )
inner join [main_tbl].dbo.downloads d with ( nolock ) on ( ( d.download_id = txn.download_id ) )
inner join [main_tbl].dbo.download_types dt with ( nolock ) on ( ( dt.download_type_id = d.download_type_id ) )
--inner join [LOGGING].dbo.wap_sessions wp with (nolock) on ( ( wp.client_id = txn.client_id ) )
where (txn.client_id in (50025,50015,50030,50020))
and (txn.date_created between @start_date AND @end_date) --between '2007-01-01' And '2007-01-10')
and (txn.billing_successful = 1)
and (txn.PORTAL_CAMPAIGN_ID is null)
--and (dt.download_type_id = '104' + '110') --choose downloadtype
--and (dt.download_type_id = 110) --ch+oose downloadtype
--and (dt.download_type_id = 104) --ch+oose downloadtype
group by
c.client_ID, datepart(day,txn.date_created), dt.download_type_id, dt.download_type_id
order by
c.client_ID, dt.download_type_id[/brown]
any help will be VERY VERY VERY appreciated.
Thanks
James
-
You use CASE ... WHEN for conditionals in SQL Server. Use CASE WHEN to translate the download type into a 1 or zero result, then SUM() the 1's and zeroes per client. Your SELECT would look something like this:
select
c.client_id,
@start_date [date],
SUM( CASE WHEN dt.download_type_id = 104 THEN 1 ELSE 0 END) As CountWallpaper,
SUM( CASE WHEN dt.download_type_id = 110 THEN 1 ELSE 0 END) As CountVideo
... rest of SQL here ...
GROUP BY c.client_id
-
Thats great thanks. That worked perfectly. such a simple solution too.
I ever so thankful, that's been a headache for a long time.
Cheers!
Jim!
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply