help modify sql please - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

help modify sql please

Author

Topic

hafidz
Starting Member

11 Posts

Posted - 2007-05-30 : 07:35:18

can help me to modify this sql so that it will return all "id". Now only return id that got BOTH broadcast type email AND broadcast type mobile only.

thanks

SELECT D.ID AS ACCOUNT_ID, D.NAME, sum(R.TOTAL_SENT) AS 

TOTAL_EMAIL, sum(R2.TOTAL_SENT) AS TOTAL_SMS
FROM DOMAINS D
left JOIN BCAST_RUNS R ON D.ID=R.DOMAIN_ID

left join broadcasts b on b.id = r.broadcast_id

left JOIN BCAST_RUNS R2 ON D.ID=R2.DOMAIN_ID
left join broadcasts b2 on b2.id = r2.broadcast_id

where b.BROADCAST_TYPE = 'email'
AND b2.BROADCAST_TYPE = 'mobile'
group by d.id,d.name
ORDER BY D.NAME

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-30 : 07:37:50

[code]
SELECT D.ID AS ACCOUNT_ID, D.NAME, sum(R.TOTAL_SENT) AS

TOTAL_EMAIL, sum(R2.TOTAL_SENT) AS TOTAL_SMS
FROM DOMAINS D
left JOIN BCAST_RUNS R ON D.ID=R.DOMAIN_ID

left join broadcasts b on b.id = r.broadcast_id
and b.BROADCAST_TYPE = 'email'
left JOIN BCAST_RUNS R2 ON D.ID=R2.DOMAIN_ID
left join broadcasts b2 on b2.id = r2.broadcast_id
and b2.BROADCAST_TYPE = 'mobile'
where b.BROADCAST_TYPE = 'email'
AND b2.BROADCAST_TYPE = 'mobile'
group by d.id,d.name
ORDER BY D.NAME
[/code]

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-30 : 07:38:49

Sure. Pretty easily. Just remove the WHERE clause!

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

hafidz
Starting Member

11 Posts

Posted - 2007-05-30 : 21:46:02

quote:

Originally posted by khtan


SELECT D.ID AS ACCOUNT_ID, D.NAME, sum(R.TOTAL_SENT) AS 

TOTAL_EMAIL, sum(R2.TOTAL_SENT) AS TOTAL_SMS
FROM DOMAINS D
left JOIN BCAST_RUNS R ON D.ID=R.DOMAIN_ID

left join broadcasts b on b.id = r.broadcast_id
                      and b.BROADCAST_TYPE = 'email'
left JOIN BCAST_RUNS R2 ON D.ID=R2.DOMAIN_ID
left join broadcasts b2 on b2.id = r2.broadcast_id
                       and b2.BROADCAST_TYPE = 'mobile'
where b.BROADCAST_TYPE = 'email'
AND b2.BROADCAST_TYPE = 'mobile'
group by d.id,d.name
ORDER BY D.NAME

cannot.. it return sum of all mobile and sms for each id..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-30 : 21:47:18

Post your table DDL, sample data and the result that you want

hafidz
Starting Member

11 Posts

Posted - 2007-05-30 : 22:43:55

quote:
Originally posted by khtan

Post your table DDL, sample data and the result that you want

KH

table : domain
--------------
id | name
1 , john
2 , jane

table : bcast_run
id | domain_id | broadcast_id | total_sent
------------------------------------------
1 , 1 , 1 , 10
2 , 1 , 2 , 20
3 , 2 , 1 , 30
4 , 2 , 2 , 40

table : broadcast
id | broadcast_type
-------------------
1 , email
2 , mobile

i want the result to be like this :-

account_id | name | total_email | total_sms
-------------------------------------------
1 , john , 10 , 20
2 , jane , 30 , 40

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-30 : 23:48:41

try this..

Select a.id [Account Id], min(name) [Name],
Sum(coalesce(case when broadcast_type = 'email' then total_sent end,0)) as 'Email Sent',
Sum(coalesce(case when broadcast_type = 'mobile' then total_sent end,0)) as 'SMS Sent' from @domain a left join @bcast_run b
on a.id = b.domain_id left join @broadcast c
on b.broadcast_id = c.id
group by a.id

--------------------------------------------------
S.Ahamed

hafidz
Starting Member

11 Posts

Posted - 2007-05-31 : 00:09:46

cannot

got this:-

ISC ERROR CODE:335544569

ISC ERROR MESSAGE:
Dynamic SQL Error
SQL error code = -104
Token unknown - line 1, column 22
Id

quote:
Originally posted by pbguy

try this..

Select a.id [Account Id], min(name) [Name],
Sum(coalesce(case when broadcast_type = 'email' then total_sent end,0)) as 'Email Sent',
Sum(coalesce(case when broadcast_type = 'mobile' then total_sent end,0)) as 'SMS Sent' from @domain a left join @bcast_run b
on a.id = b.domain_id left join @broadcast c
on b.broadcast_id = c.id
group by a.id

--------------------------------------------------
S.Ahamed

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-31 : 00:11:58

How you are using the query..it seems ur using dynamic sql.
Is it possible to for u to post the actual query where this query is used.

--------------------------------------------------
S.Ahamed

hafidz
Starting Member

11 Posts

Posted - 2007-05-31 : 00:29:33

quote:
Originally posted by pbguy

How you are using the query..it seems ur using dynamic sql.
Is it possible to for u to post the actual query where this query is used.

--------------------------------------------------
S.Ahamed

?? what do u mean?

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-31 : 00:39:46

i meant, Are you using dynamic SQL ? and

Can you post your whole query?

--------------------------------------------------
S.Ahamed

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-31 : 01:04:36

[code]
declare @domain table
(
[id] int,
[name] varchar(10)
)
insert into @domain
select 1 , 'john' union all
select 2 , 'jane'

declare @bcast_run table
(
[id] int,
domain_id int,
broadcast_id int,
total_sent int
)

insert into @bcast_run
select 1 , 1 , 1 , 10 union all
select 2 , 1 , 2 , 20 union all
select 3 , 2 , 1 , 30 union all
select 4 , 2 , 2 , 40

declare @broadcast table
(
[id] int,
broadcast_type varchar(10)
)
insert into @broadcast
select 1 , 'email' union all
select 2 , 'mobile'

select d.[id], d.[name],
total_email = sum(case when b.broadcast_type = 'email' then total_sent else 0 end),
total_sms = sum(case when b.broadcast_type = 'mobile' then total_sent else 0 end)
from @domain d inner join @bcast_run r
on d.[id] = r.domain_id
inner join @broadcast b
on r.broadcast_id = b.[id]
group by d.[id], d.[name]
/*
id name total_email total_sms
----------- ---------- ----------- -----------
1 john 10 20
2 jane 30 40
*/
[/code]

hafidz
Starting Member

11 Posts

Posted - 2007-05-31 : 04:23:59

quote:

Originally posted by khtan


declare @domain table
(
	[id]	int,
	[name]	varchar(10)
)
insert into @domain
select	1 , 'john' union all
select	2 , 'jane'

declare @bcast_run table
(
	[id]		int,
	domain_id	int,
	broadcast_id	int,
	total_sent	int
)

insert into @bcast_run
select	1 , 1 , 1 , 10 union all
select	2 , 1 , 2 , 20 union all
select	3 , 2 , 1 , 30 union all
select	4 , 2 , 2 , 40

declare @broadcast table
(
	[id]		int,
	broadcast_type	varchar(10)
)
insert into @broadcast
select	1 , 'email'  union all
select	2 , 'mobile'

select	d.[id], d.[name],
	total_email	= sum(case when b.broadcast_type = 'email' then total_sent else 0 end),
	total_sms	= sum(case when b.broadcast_type = 'mobile' then total_sent else 0 end)
from	@domain d inner join @bcast_run r
	on	d.[id]	= r.domain_id
	inner join @broadcast b
	on	r.broadcast_id	= b.[id]
group by d.[id], d.[name]
/*
id          name       total_email total_sms   
----------- ---------- ----------- ----------- 
          1 john                10          20 
          2 jane                30          40 
*/

error man

ISC ERROR CODE:335544569

ISC ERROR MESSAGE:
Dynamic SQL Error
SQL error code = -104
Token unknown - line 2, column 14
=

hafidz
Starting Member

11 Posts

Posted - 2007-05-31 : 04:26:20

quote:
Originally posted by pbguy

i meant, Are you using dynamic SQL ? and

Can you post your whole query?

--------------------------------------------------
S.Ahamed

the whole query is in my first post

i donno whether im using dynamic or static sql.. but uses firebird 2.0 db

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-31 : 05:11:05

This is SQL server forum and Post in the respective forum

--------------------------------------------------
S.Ahamed

hafidz
Starting Member

11 Posts

Posted - 2007-05-31 : 06:56:28

quote:
Originally posted by pbguy

This is SQL server forum and Post in the respective forum

--------------------------------------------------
S.Ahamed

please help to move there.. thanks

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-31 : 07:49:33

Google is the great helper..

Search for Firebird db forum u will get more results, find one go ahead.

--------------------------------------------------
S.Ahamed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources