select help needed - 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)

select help needed

Author

Topic

palak
Yak Posting Veteran

55 Posts

Posted - 2008-02-19 : 14:25:24

i m getting results like

metrics mdate items total
------------------------------------------------
001awer 2007-1-14 1 100.00
net100 2007-1-14 1 10.00
214sdf 2007-1-14 1 25.00
net16wrew 2007-2-14 2 80.00

so in short i want values of same items - total
but different metrics

like

metrics mdate items total
--------------------------------------------------------
001awer 2007-1-14 1 135.00
net100
net100
net16wrew 2007-2-14 2 80.00

how should i get this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-19 : 23:49:31

Are you trying to show this in some reports? The data format you request seems like its for a report. if yes, please try to do this at front end,. the result you request can be obtained by

SELECT t1.metrics,t1.mdate,t1.items,t2.total
FROM table t1
INNER JOIN (SELECT mdate,SUM(total) as total
FROM Table
GROUP BY mdate) t2
on t2.mdate=t1.mdate

i'm assuming all fields are from same table. if not,please post your table structure for complete soln.

palak
Yak Posting Veteran

55 Posts

Posted - 2008-02-20 : 08:26:29

yes, this is for report,

and i have metrics and mdate in one table and items and total in another table...

i have done like this:

SELECT t1.metrics,t1.mdate,t1.items,t2.total
FROM table1 t1
right outer JOIN (SELECT items,SUM(total) as total
FROM Table2 t2
GROUP BY items) t2
on t2.items=t1.items

but it's not giving me correct output which i mentioned...

thanks

palak
Yak Posting Veteran

55 Posts

Posted - 2008-02-20 : 08:38:54

i m creating view for this query:

create view
as
SELECT distinct t1.metrics,t1.mdate,t2.items,t2.total
FROM table1 t1
INNER JOIN (SELECT distinct items,SUM(total) as total
FROM Table2 t2
GROUP BY items) t2
on t2.items=t1.items
Go

while executingt thsi view:

select * from view
where mdate='1/17/2008' and items='55-248'

i should get only one row
001awer 2008-01-17 55-248 100.00

instead of that i m getting 10 rows
001awer 2008-01-17 55-248 10.00
001awer 2008-01-17 55-248 10.00
001awer 2008-01-17 55-248 10.00
001awer 2008-01-17 55-248 10.00
001awer 2008-01-17 55-248 10.00
001awer 2008-01-17 55-248 10.00
001awer 2008-01-17 55-248 10.00
001awer 2008-01-17 55-248 10.00
001awer 2008-01-17 55-248 10.00
001awer 2008-01-17 55-248 10.00

can anybody try to help me to get correct output

thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-03-04 : 11:49:22

select metrics,item,mdate,sum(total) as total
from view
where mdate='20080117' and items='55-248'
group by metrics,item,mdate

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources