October 24, 2008 at 8:28 am
I'm using DBaccess and I have a Table includind these columns A,B,C,D,E,F,G,H
I used this SQL only for B,C,D,E,F,G,H. It succeed with me.
" SELECT B,C,D,E, SUM(F),LAST(G),LAST(H)FROM Table1 GROUP BY B,C,D,E "I want to join these three statments SQL in SQL previous instead of SUM(F)
I want to join these three statments SQL in SQL previous instead of SUM(F)
(SELECT B,C,D,E, SUM(F) Where A='Buy' GROUP BY B,C,D,E ") _
+((SELECT B,C,D,E, SUM(F) Where A='Sell' GROUP BY B,C,D,E ")-(SELECT B,C,D,E, SUM(F) _
Where A='Damage' GROUP BY B,C,D,E "))
October 24, 2008 at 9:08 am
If I understand the question correctly, you're looking for the UNION operator. To combine the three statements:
SELECT...
FROM....
WHERE...
UNION
SELECT...
FROM..
WHERE...
UNION
SELECT...
FROM...
WHERE...
[/code]
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 24, 2008 at 11:16 am
Is this correct?
SELECT B,C,D,E, UNION(SELECT B,C,D,E, SUM(F) Where A='Buy' GROUP BY B,C,D,E ") +((SELECT B,C,D,E, SUM(F) Where A='Sell' GROUP BY B,C,D,E ")-(SELECT B,C,D,E, SUM(F) Where A='Damage' GROUP BY B,C,D,E ")),LAST(G),LAST(H)FROM Table1 GROUP BY B,C,D,E "
October 24, 2008 at 11:38 am
OK, you lost me. I can't tell what you're trying to do there.
You can't subtract one result set from another one and you have to have FROM clauses to define where you're getting the data. A UNION looks something like this:
SELECT B
,C
,D
,E
,F
FROM something
UNION
SELECT B
,C
,D
,E
,SUM(F)
FROM somethingelse
WHERE A = 'Buy'
GROUP BY B
,C
,D
,E
UNION
SELECT B
,C
,D
,E
,SUM(F)
FROM anotherthing
WHERE A = 'Sell'
GROUP BY B
,C
,D
,E
But after that last post, I'm not sure that's what you need. What are you trying to do?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 24, 2008 at 11:39 am
By the way, what's DBAccess?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 24, 2008 at 12:15 pm
Grant Fritchey (10/24/2008)
By the way, what's DBAccess?
Yes, DBAccess and I'm using one Table
October 24, 2008 at 12:25 pm
Mangore75 (10/24/2008)
Grant Fritchey (10/24/2008)
By the way, what's DBAccess?Yes, DBAccess and I'm using one Table
The question is, what is it? This is a SQL Server forum. Is that a tool for querying SQL Server or is it a different data management tool entirely?
It makes a difference because I'm trying to supply you with information about SQL Server. If that's not what you need, I'd hate be hurting more than I'm helping.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 24, 2008 at 12:48 pm
Mangore75 (10/24/2008)
Grant Fritchey (10/24/2008)
By the way, what's DBAccess?Yes, DBAccess and I'm using one Table
do you mean MS access ? or is DBAccess a third party tool for querying sql server?
October 24, 2008 at 1:43 pm
MS access.
October 24, 2008 at 1:45 pm
I give up trying to understand the question 😛
October 25, 2008 at 3:21 am
And the query you're trying to write is an Access query, or you're using Access to connect to SQL Server and you're trying to write a SQL Server query?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 25, 2008 at 5:13 am
I'm trying to connect from Vb2005 to Ms Access
and here is the solution for my Question
" SELECT B,C,D,E,SUM( IIF(A='Buy', F, -F,) ),LAST(G),LAST(H) FROM Table1 Where A IN ('Buy','Sell','Damage') GROUP BY B,C,D,E"
By the way What is the difference between Ms Access and DBAccess.
October 25, 2008 at 5:17 am
Access is Microsofts small scale relational data management tool. I wasn't sure what you meant by DBAccess. IBM DB2 has a program called DB-Access that is for querying databases.
Since we're finally clear, I'm not sure how to help. I don't know Access queries that well. You're posting in a SQL Server 2008, yet another product, forum. I believe there is an Access forum where guys who really know that product can help out.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 25, 2008 at 7:59 am
any way thank you for help.
For my question I got it as I told you in previous post.
Thank you again.
Viewing 14 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply