Sql Query(Urgent!!!) - 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

Other Forums

MS Access

Sql Query(Urgent!!!)

Author

Topic

subscriber
Starting Member

8 Posts

Posted - 2006-01-07 : 11:11:29

Records in table are so:

customer_id process total
1 1 10
2 1 20
3 1 25
1 0 5
3 0 20

i wantto subtract total values which process column value 1 from which process column value 0 for each customer_id and list by customer_id.

And i wantto sum(total) value of this query.

how can be this sql query?

sorry my bad english:(

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-01-07 : 11:29:54

[code]create table #x(customer_id int,process int,total int)

insert #x
select 1, 1, 10
union all select 2, 1, 20
union all select 3, 1, 25
union all select 1, 0, 5
union all select 3, 0, 20

--select * from #x

select
case grouping(customer_id) when 1 then 'TOTAL' else ltrim(customer_id) end as customer_id
,sum(case process when 1 then total else 0 end) as process1
,sum(case process when 0 then total else 0 end) as process0
,sum(case process when 1 then total else 0 end)
-sum(case process when 0 then total else 0 end) as process1_minus_process0
from
#x
group by
customer_id
with rollup
order by
grouping(customer_id)
,customer_id

drop table #x

customer_id process1 process0 process1_minus_process0
------------ ----------- ----------- -----------------------
1 10 5 5
2 20 0 20
3 25 20 5
TOTAL 55 25 30[/code]

rockmoose

subscriber
Starting Member

8 Posts

Posted - 2006-01-07 : 11:55:10

select
case grouping(customer_id) when 1 then 'TOTAL' else ltrim(customer_id) end as customer_id
,sum(case process when 1 then total else 0 end) as process1
,sum(case process when 0 then total else 0 end) as process0
,sum(case process when 1 then total else 0 end)
-sum(case process when 0 then total else 0 end) as process1_minus_process0
from
#x
group by
customer_id
with rollup
order by
grouping(customer_id)
,customer_id

when i write this sqlquery to access database i get error for "groupping"???

subscriber
Starting Member

8 Posts

Posted - 2006-01-07 : 12:00:34

and i wantto output so :

customer_id process1-process0
1 5
2 20
3 5
total 55

subscriber
Starting Member

8 Posts

Posted - 2006-01-07 : 12:01:27

sorry total=30

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-01-07 : 12:31:10

Oh, this is Access.
Well then I don't know.
Access does not know the "grouping" keyword, and I'm not sure about the "case" keyword either.

Post this question in the Access forum...

Said and done:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=59939

rockmoose

subscriber
Starting Member

8 Posts

Posted - 2006-01-09 : 17:57:23

i think this is possible with iif function that equavalent of case when but how:(

subscriber
Starting Member

8 Posts

Posted - 2006-01-11 : 05:53:58

yes problem solved by me:)
query is:
select cust_id
,sum(iif(process=1,[total],0)) as process1
,sum(iif(process=0,[total],0)) as process0
,sum(iif(process=1,[total],0)) - sum(iif(process=0,[total],0))as process1_minus_process0
from
tablename
group by
cust_id
having
sum(iif(process=1,[total],0)) - sum(iif(process=0,[total],0)) >0
order by
cust_id

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources