December 18, 2008 at 2:49 am
hi i am having three table which related to each other i need to take the sum of coumn A from table T1 by group clause and where coumn B=y and from same table i will be sum of coumn A from table T1 by group clause and where coumn B=n and i have to take the difference of sum from result2 and result1 with all the coulmn from table T1.
can pls some one help on this,thankyou
December 18, 2008 at 2:54 am
Can you please post the table description in the way described in the link in my signature please? If you also throw in some sample input data and the required output, I'm sure we can give you a good solution quickly. SQL Server nerds read SQL quicker than english 🙂
Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2
December 18, 2008 at 3:12 am
Without any select query example , this is the best I can come up with 😉
To get you on track ...
Select a.col1 as mygroupcolumn
, sum(b.colx) as sum_colx
, sum(c.col1a) as sum_col1a
from table1 a
inner join tableB b
on b.FKTable1 = a.pk
inner join table3 c
on c.FKtableB = b.pk
group by a.col1
order by mygroupcolumn ;
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 18, 2008 at 3:38 am
1st query--
select sum(table1.coumn1),table1.column2,table2.column3 Table1
inner join table2 on table1.coulmn3=table2.column4
where table1.column2='Y'
group by column2,column3
2nd query--
select sum(table1.coumn1),table1.column2,table2.column3 from Table1
inner join table2 on table1.coulmn3=table2.column4
where table1.column2='N'
group by column2,column3
but it has to be combined in this way---
but the result which i need should be of this type select [sum(table1.column1 where table1.coulmn2='N')-sum(table1.coumn1 where table1.coulmn2='Y')]
inner join table2 on table1.coulmn3=table2.column4
where table1.column2='Y'
group by column2,column3
December 18, 2008 at 4:12 am
It shows you haven't looked at the link I suggested, but your query gives enough input for now... I think this is what you would need:select sum(CASE table1.column2 WHEN 'N' THEN table1.column1 ELSE 0 END - CASE table1.column2 WHEN 'Y' THEN table1.column1 ELSE 0 END),
table1.column2,
table2.column3
from Table1
inner join table2 on table1.column3 = table2.column4
where table1.column2='N'
group by column2,column3
Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2
December 18, 2008 at 4:20 am
Join table1 twice...
SELECT...
FROM table2 t2
INNER JOIN table1 y ON y.coulmn3 = t2.column4 AND y.column2='Y'
INNER JOIN table1 n ON n.coulmn3 = t2.column4 AND n.column2='N'
GROUP BY...
If this fails because of the aggregate, then try with preaggregated derived tables...
SELECT...
FROM table2 t2
INNER JOIN (SELECT ... FROM table1 WHERE y.column2='Y' GROUP BY column2, column3) y ON y.coulmn3 = t2.column4
INNER JOIN (SELECT ... FROM table1 WHERE y.column2='N' GROUP BY column2, column3) n ON n.coulmn3 = t2.column4
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 19, 2008 at 4:32 am
no it doesnt work
December 19, 2008 at 4:37 am
santosh.lamane (12/19/2008)
no it doesnt work
Could you elaborate please, Santosh? Post the code, and the error message? Thanks.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 19, 2008 at 4:54 am
same player shoot again ... hit ball when lights are on ...
Select *
, Sum_Y - Sum_N as Subtracted
from (
select sum(CASE table1.column2 WHEN 'N' THEN table1.column1
ELSE 0
END ) as Sum_N
, sum (CASE table1.column2
WHEN 'Y' THEN table1.column1
ELSE 0
END) as Sum_Y
-- table1.column2,
table2.column3
from Table1
inner join table2 on table1.column3 = table2.column4
where table1.column2 in ('N', 'Y')
group by column3
) SumTb
order by column3
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 19, 2008 at 5:11 am
ALZDBA (12/19/2008)
same player shoot again ... hit ball when lights are on ...
:D:D
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 20, 2008 at 2:35 am
Hi Thankyou very much its working
and now i am trying to export the data using bcp
using below code where output is stored procedure which i had writen and it is just the select statement.
where i run the store procedure it give me exact result of count 4000 but when i run the below commad to export the data in excel format it gives me 3489 records. and i need to have the heading along the exported data can please help me on this.
DECLARE @FileName varchar(50),
@bcpCommand varchar(8000)
SET @FileName = REPLACE('d:\Item'+CONVERT(char(8),GETDATE(),1)+'.xls','/','-')
SET @bcpCommand = 'bcp "exec orbit..output" queryout "'
SET @bcpCommand = @bcpCommand + @FileName + '" -t -T -S'+ @@servername
EXEC master..xp_cmdshell @bcpCommand
December 20, 2008 at 3:02 am
Well, this is sql2005
xp_cmdshell is off by default.
You would be better off just exporting the result using SQLCMD or DBMail if you want to email it.
Check out BOL
e.g
sqlcmd -s yourserver\instance -d yourdb-Q "SELECT FirstName, LastName FROM Person.Contact WHERE LastName LIKE 'Whi%';" -O c:\temp\theresult.txt
The are a couple of nice articles at SSC whic show about excell,
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 20, 2008 at 4:01 am
hi, its working thanks a lot
now i am trying the export the data in excel format using bcp code.
i had written the simple procedure of select statement.
now if run only query it give me excat result set but when i export the data i wont get excat data. and i need the heading of coulmn but it wont reflect.
DECLARE @FileName varchar(50),
@bcpCommand varchar(8000)
SET @FileName = REPLACE('d:\Item_Report'+CONVERT(char(8),GETDATE(),1)+'.xls','/','-')
SET @bcpCommand = 'bcp "exec test..out" queryout "'
SET @bcpCommand = @bcpCommand + @FileName + '" -c -T -S'+ @@servername
EXEC master..xp_cmdshell @bcpCommand
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply