Union Result Problem

Mar 20, 2006

I'm having a bit of trouble with the results I'm getting from a union query. First query and results shows that the 5th column(pmt) is being doubled, but if I run just that section of the union, it's not doubled. Correct result should NOT be doubled. Any help? many, many thanks.

select v.eventfee_pk, r.event_fk, r.person_fk, 0 as fee, isNull(p.payment_amount,0) as pmt, registration_ts as regDate from tblRegistration r right join tblRegistrationFee f on r.registration_pk=f.registration_fk left join tblEventFee v on f.eventfee_fk=v.eventfee_pk left join tblPayment p on r.registration_pk=p.registration_fk where (r.registration_ts >= '3/1/2006 00:00:01' and r.registration_ts <= '3/20/2006 23:59:59') and (r.person_fk=111918)
union
select v.eventfee_pk, r.event_fk, r.person_fk, isNull(v.event_fee,0) as fee, 0 as pmt, registration_ts as regDate from tblRegistration r right join tblRegistrationFee f on r.registration_pk=f.registration_fk left join tblEventFee v on f.eventfee_fk=v.eventfee_pk where (r.registration_ts >= '3/1/2006 00:00:01' and r.registration_ts <= '3/20/2006 23:59:59') and (r.person_fk=111918)


2510315089111918725.0000 .0000 2006-03-11
2510515089111918120.0000 .0000 2006-03-11
2510315089111918.0000 845.0000 2006-03-11
2510515089111918.0000 845.0000 2006-03-11


select v.eventfee_pk, r.event_fk, r.person_fk, isNull(v.event_fee,0) as fee, 0 as pmt, registration_ts as regDate from tblRegistration r right join tblRegistrationFee f on r.registration_pk=f.registration_fk left join tblEventFee v on f.eventfee_fk=v.eventfee_pk where (r.registration_ts >= '3/1/2006 00:00:01' and r.registration_ts <= '3/20/2006 23:59:59') and (r.person_fk=111918)

2510315089111918725.0000 02006-03-11
2510515089111918120.0000 02006-03-11

View 2 Replies


ADVERTISEMENT

UNION ALL, The Use Of GO In Stored Proc. And The Result Is?

Aug 29, 2006

I hope I can express what I need to know.Here it goes. I am planning on writing a single stored procedure to include two select statements and followed by a third like so:Select * etcUNION ALLSelect * etcGOSelect something elseMy question is this: does this return one record set (returning all records from the three Select statements) or two, the first being the result from the UNION and the second from the select following the GO. I will need to access the data returned from the resultant UNION and from the select after the Go, so I wonder if I'll be dealing with a single dataset or two. I hope this made sense.  

View 3 Replies View Related

Create Union Query Result

Jun 20, 2008

Hi,I was wondering if someone can help me.  I have 4 tables in an SQL Server 2005 database, for purposes sake called 'table1', 'table2', 'table3' and 'table4'. They all have the same data structure between them.  The columns inside them have the exact same design.  For purposes sake called 'column1', 'column2', 'column3' and 'column4'.  Alot of these tables have duplicate records scattered between them, so basically I want to create 'table5' with the same column names but only unique records.  Can someone specify the syntax I need for this.  I'm pretty sure it's a union query I need so am scouring as I type this also.   Many Thanks Robert  

View 5 Replies View Related

How Is The Metadata Of The Result Of A UNION Determined?

May 23, 2006

Hi,

A colleague and I have just found a slightly strange situation that we don't understand.

We had (effectively) the following query:

select cast(1 as decimal(38,10))
union all
select cast(1 as decimal(38,4))

And the result contained 2 rows, each with a a scale of 4. This surprised us, we expected that the metadata of the result would be determined by the topmost query.

So we reversed them and tried this:

select cast(1 as decimal(38,4))
union all
select cast(1 as decimal(38,10))

and got exactly the same result. 2 rows with a scale of 4.

We can't understand why the scale always gets determined to be 4 regardless of the order of the queries.



Any explanation would be much appreciated!



Thanks

Jamie

View 4 Replies View Related

How To Rank An Union Of Two Result Sets?

Mar 1, 2007

I am new to SQL Server Integration Services. I need to help here.

If there is only one result set, I could get rank info by putting DENSE_RANK expression inside of my select. My case is that I have two data source. After union of them, I need rank the result set and save result set plus rank info into the destination. I tried to use Derived Column component by using expression DENSE_RANK() over ( order by columnName ). But it does not like it.

Does anyone know how to achieve this?

Thanks!

View 3 Replies View Related

TSQL UNION, But Get The Differance In Dollar Amount In The Result

May 26, 2006

GridView_1
Category Name SubCategory Name Amount
Construction Construction 2,877.00
Design Design 0.00
Soft Cost Inspection 0.00

GridView_2
Category Name SubCategory Name Amount
Construction Construction 2,800.00
Construction Contingency 300.00
Design Design 500.00
Soft Cost Inspection 980.00
Soft Cost Survey 145.00
Soft Cost Testing 720.00
Soft Cost Management 1000.00
Soft Cost Other Costs 10,000.00

QUESTION:
How could I UNION the two result sets together and where the Category Name and SubCategory Name are equal for GridView1 and GridView2, show the difference in the Amount column.

This is the result I am looking for.


Category Name SubCategory Name DIFFERANCE
Construction Construction 77.00 = (GridView1 - GridView2)
Construction Contingency 300.00
Design Design 500.00
Soft Cost Inspection 980.00
Soft Cost Survey 145.00
Soft Cost Testing 720.00
Soft Cost Management 1000.00
Soft Cost Other Costs 10,000.00

I am trying to do it on the back end. Though, if you have a clever way to produce a resulting gridview in C# with the results I need, I am all game!
This is what I have so far: (TSQL code)



SELECT

tblCategories.txtCategoryName,

tblSubCategories.txtSubCategoryName,

tblEstimatesLineItems.curEscAmount

FROM

tblEstimatesLineItems, tblCategories, tblSubCategories

WHERE

tblEstimatesLineItems.lngzEstimateId = 24 AND --@lngzEstimateId_Compare1

tblEstimatesLineItems.lngzCategoryId = tblCategories.idsCategoryId AND

tblEstimatesLineitems.lngzSubCategoryId = tblSubCategories.idsSubCategoryId

union all

SELECT

tblCategories.txtCategoryName,

tblSubCategories.txtSubCategoryName,

tblEstimatesLineItems.curEscAmount

FROM

tblEstimatesLineItems, tblCategories, tblSubCategories

WHERE

tblEstimatesLineItems.lngzEstimateId = 25 AND --@lngzEstimateId_Compare1

tblEstimatesLineItems.lngzCategoryId = tblCategories.idsCategoryId AND

tblEstimatesLineitems.lngzSubCategoryId = tblSubCategories.idsSubCategoryId

View 7 Replies View Related

T-SQL (SS2K8) :: Using Union ALL Or Union Kills Performance On Stored Proc

Jun 12, 2014

SQL Server 2008 r2...

I have a query which does 3 selects and Union ALLs each to get a final result set. The performance is unacceptable - takes around a minute to run. If I remove the Union All so that the result sets are returned individually it returns all 3 from the query in around 6 seconds (acceptable performance).

Any way to join the result sets together without using Union All.

Each result set has exactly the same structure returned...

Query below [for reference]...

WITH cte AS (
SELECT A.[PoleID], ISNULL(B.[IsSpanClear], 0) AS [IsSpanClear], B.[SurveyDate], ROW_NUMBER() OVER (PARTITION BY A.[PoleID] ORDER BY B.[SurveyDate] DESC) rownum
FROM[UT_Pole] A
LEFT OUTER JOIN [UT_Surveyed_Pole] B ON A.[PoleID] = B.[PoleID]

[Code] .....

View 4 Replies View Related

How Does Union/union All Work Inside SQL Server?

Apr 29, 2008



Why the sequence different?



select * from (

select id=3,[name]='Z'

union all select 1,'G'

union all select 2,'R'

union all select 4,'Z'

) as t

order by [name]

--result:

---------

--1 G

--2 R

--4 Z

--3 Z

select * from (

select id=3,[name]='Z'

union select 1,'G'

union all select 2,'R'

union all select 4,'Z'

) as t

order by [name]

--result:

----------

--1 G

--2 R

--3 Z--changed

--4 Z

View 3 Replies View Related

Union All Does Not Union All Rows

Nov 6, 2006

Hi all,

I have a Union All transformation with 4 inputs and one output when I debug the package the sum of the different inputs rows does not match the row count in output.

I don't understand, I've used the Union All transform many times and I've never seen this.

Any idea why this could happen ?

View 18 Replies View Related

Set Variable Based On Result Of Procedure OR Update Columns Fromsproc Result

Jul 20, 2005

I need to send the result of a procedure to an update statement.Basically updating the column of one table with the result of aquery in a stored procedure. It only returns one value, if it didnt Icould see why it would not work, but it only returns a count.Lets say I have a sproc like so:create proc sp_countclients@datecreated datetimeasset nocount onselect count(clientid) as countfrom clientstablewhere datecreated > @datecreatedThen, I want to update another table with that value:Declare @dc datetimeset @dc = '2003-09-30'update anothertableset ClientCount = (exec sp_countclients @dc) -- this line errorswhere id_ = @@identityOR, I could try this, but still gives me error:declare @c intset @c = exec sp_countclients @dcWhat should I do?Thanks in advance!Greg

View 4 Replies View Related

Problem Assigning SQL Task Result To A Variable - Select Count(*) Result From Oracle Connection

Dec 26, 2007



I have an Execute SQL Task that executes "select count(*) as Row_Count from xyztable" from an Oracle Server. I'm trying to assign the result to a variable. However when I try to execute I get an error:
[Execute SQL Task] Error: An error occurred while assigning a value to variable "RowCount": "Unsupported data type on result set binding Row_Count.".

Which data type should I use for the variable, RowCount? I've tried Int16, Int32, Int64.

Thanks!

View 5 Replies View Related

Table-valued User-defined Function: Commands Completed Successfully, Where Is The Result? How Can I See Output Of The Result?

Dec 11, 2007

Hi all,

I copied the following code from Microsoft SQL Server 2005 Online (September 2007):
UDF_table.sql:

USE AdventureWorks;

GO

IF OBJECT_ID(N'dbo.ufnGetContactInformation', N'TF') IS NOT NULL

DROP FUNCTION dbo.ufnGetContactInformation;

GO

CREATE FUNCTION dbo.ufnGetContactInformation(@ContactID int)

RETURNS @retContactInformation TABLE

(

-- Columns returned by the function

ContactID int PRIMARY KEY NOT NULL,

FirstName nvarchar(50) NULL,

LastName nvarchar(50) NULL,

JobTitle nvarchar(50) NULL,

ContactType nvarchar(50) NULL

)

AS

-- Returns the first name, last name, job title, and contact type for the specified contact.

BEGIN

DECLARE

@FirstName nvarchar(50),

@LastName nvarchar(50),

@JobTitle nvarchar(50),

@ContactType nvarchar(50);

-- Get common contact information

SELECT

@ContactID = ContactID,

@FirstName = FirstName,

@LastName = LastName

FROM Person.Contact

WHERE ContactID = @ContactID;

SELECT @JobTitle =

CASE

-- Check for employee

WHEN EXISTS(SELECT * FROM HumanResources.Employee e

WHERE e.ContactID = @ContactID)

THEN (SELECT Title

FROM HumanResources.Employee

WHERE ContactID = @ContactID)

-- Check for vendor

WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc

INNER JOIN Person.ContactType ct

ON vc.ContactTypeID = ct.ContactTypeID

WHERE vc.ContactID = @ContactID)

THEN (SELECT ct.Name

FROM Purchasing.VendorContact vc

INNER JOIN Person.ContactType ct

ON vc.ContactTypeID = ct.ContactTypeID

WHERE vc.ContactID = @ContactID)

-- Check for store

WHEN EXISTS(SELECT * FROM Sales.StoreContact sc

INNER JOIN Person.ContactType ct

ON sc.ContactTypeID = ct.ContactTypeID

WHERE sc.ContactID = @ContactID)

THEN (SELECT ct.Name

FROM Sales.StoreContact sc

INNER JOIN Person.ContactType ct

ON sc.ContactTypeID = ct.ContactTypeID

WHERE ContactID = @ContactID)

ELSE NULL

END;

SET @ContactType =

CASE

-- Check for employee

WHEN EXISTS(SELECT * FROM HumanResources.Employee e

WHERE e.ContactID = @ContactID)

THEN 'Employee'

-- Check for vendor

WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc

INNER JOIN Person.ContactType ct

ON vc.ContactTypeID = ct.ContactTypeID

WHERE vc.ContactID = @ContactID)

THEN 'Vendor Contact'

-- Check for store

WHEN EXISTS(SELECT * FROM Sales.StoreContact sc

INNER JOIN Person.ContactType ct

ON sc.ContactTypeID = ct.ContactTypeID

WHERE sc.ContactID = @ContactID)

THEN 'Store Contact'

-- Check for individual consumer

WHEN EXISTS(SELECT * FROM Sales.Individual i

WHERE i.ContactID = @ContactID)

THEN 'Consumer'

END;

-- Return the information to the caller

IF @ContactID IS NOT NULL

BEGIN

INSERT @retContactInformation

SELECT @ContactID, @FirstName, @LastName, @JobTitle, @ContactType;

END;

RETURN;

END;

GO

----------------------------------------------------------------------
I executed it in my SQL Server Management Studio Express and I got: Commands completed successfully. I do not know where the result is and how to get the result viewed. Please help and advise.

Thanks in advance,
Scott Chang

View 1 Replies View Related

Saving Query Result To A File , When View Result Got TLV Error

Feb 13, 2001

HI,
I ran a select * from customers where state ='va', this is the result...

(29 row(s) affected)
The following file has been saved successfully:
C:outputcustomers.rpt 10826 bytes

I choose Query select to a file
then when I tried to open the customer.rpt from the c drive I got this error message. I am not sure why this happend
invalid TLV record

Thanks for your help

Ali

View 1 Replies View Related

End Result Is Main Query Results Ordered By Nested Result

May 1, 2008

As the topic suggests I need the end results to show a list of shows and their dates ordered by date DESC.
Tables I have are structured as follows:

SHOWS
showID
showTitle

SHOWACCESS
showID
remoteID

VIDEOS
videoDate
showID

SQL is as follows:

SELECT shows.showID AS showID, shows.showTitle AS showTitle,
(SELECT MAX(videos.videoFilmDate) AS vidDate FROM videos WHERE videos.showID = shows.showID)
FROM shows, showAccess
WHERE shows.showID = showAccess.showID
AND showAccess.remoteID=21
ORDER BY vidDate DESC;

I had it ordering by showTitle and it worked fine, but I need it to order by vidDate.
Can anyone shed some light on where I am going wrong?

thanks

View 3 Replies View Related

CASE Function Result With Result Expression Values (for IN Keyword)

Aug 2, 2007

I am trying to code a WHERE xxxx IN ('aaa','bbb','ccc') requirement but it the return values for the IN keyword changes according to another column, thus the need for a CASE function.

WHERE GROUP.GROUP_ID = 2 AND DEPT.DEPT_ID = 'D' AND WORK_TYPE_ID IN ( CASE DEPT_ID WHEN 'D' THEN 'A','B','C' <---- ERROR WHEN 'F' THEN 'C','D ELSE 'A','B','C','D' END )

I kept on getting errors, like

Msg 156, Level 15, State 1, Line 44Incorrect syntax near the keyword 'WHERE'.
which leads me to assume that the CASE ... WHEN ... THEN statement does not allow mutiple values for result expression. Is there a way to get the SQL above to work or code the same logic in a different manner in just one simple SQL, and not a procedure or T-SQL script.

View 3 Replies View Related

Return Subquery Result For Only First Row In Result

Apr 7, 2015

I'm using a subquery to return a delivery charge line as a column in the result set. I want to see this delivery charge only on the first line of the results for each contract. Code and results are below.

declare @start smalldatetime
declare @end smalldatetime
set @start = '2015-03-22 00:00' -- this should be a Sunday
set @end = '2015-03-28 23:59' -- this should be the following Saturday

select di.dticket [Contract], di.ddate [Delivered], di.item [Fleet_No], di.descr [Description], dd.min_chg [Delivery_Chg], dd.last_invc_date [Delivery_Invoiced],

[code]....

In this example, I only want to see the delivery charge of 125.00 for the first line of contract HU004377. For simplicity I have only shown the lines for 1 contract here, but there would normally be many different contracts with varying numbers of lines, and I only want to see the delivery charge once for each contract.

View 6 Replies View Related

Strange Result - Minus Result -1

Mar 2, 2008

help strange result whan i do this



Code Snippet
SELECT unit_date, unit, ISNULL(NULLIF ((unit + DATEDIFF(mm, GETDATE(), unit_date)) % 4, 0), 4) AS new_unit
FROM dbo.empList




i try to get next unit value to next month
why i get this -1
on date




01/01/2008
1
-1

unit_date unit new_unit



01/02/2008
2
1

01/02/2008
1
4

01/01/2008
1
-1

01/02/2008
1
4

21/01/2008
1
-1

21/01/2008
1
-1

01/02/2008
1
4


TNX

View 3 Replies View Related

DTS - UNION

Jan 23, 2001

Hi,

I am using DTS to import data where the query involves the UNION .

Select a,z,y from x
UNION
select b,z,y from y;

Only the columns Z,y appearing in the output. Is there any known problem with UNION in DTS?

Thanks

View 3 Replies View Related

Union -- Union All

Dec 3, 2004

Help me again . :) ..
Query :

Select 1 AS ColA, ColB,ColC
FROM Table1
Where ColA = 1
Union
Select 2 AS ColA, ColB,ColC
FROM Table1
Where ColA = 2

Expected Result.
ColA ColB ColC
1 Apple Sweet
1 Wine Alcohol
2 Home Nice
2 Travel Fun

But gives
ColA ColB ColC
1 Apple Sweet
1 Wine Alcohol
1 Home Nice
1 Travel Fun

any suggestions .please reply.

View 1 Replies View Related

Union In MDX

Mar 23, 2007

Could someone explain me Union function in MDX? How is different from the T-SQL Union?

My need is urgent.

View 1 Replies View Related

Union

Jul 12, 2004

i have a table with country names

table name:country
column name:country
it is populated with:
UK
USA
africa
asia
japan

i want a query which gives the foll output
USA
UK
africa
asia
japan


(i.e) USA and UK must be on the top 2 rows
while the rest of the countries should come below these 2 and sorted in the ascending order.

if it is possible with any other query also it is ok

View 1 Replies View Related

Use TOP With UNION

Jul 14, 2004

Hi

I just found out that I can do an ORDER BY clause on entire records set retrieve from a query that combines several sub queries with UNION from different tables with the same structure... so this is great to know, BTW, is this a new feature of MSSQL 2K ? I don't recall being able to do this in MSSQL 7 or 6.5.

Anyway, the main question is, can I use the TOP command in a query that has UNION in it?? Meaning, there are two queries (or more) from two tables (or more) and I need to fetch the top 10 records by an ORDER BY clause from the combined results, when I try to add each sub query TOP 10 the results are not correct at all, when I try to add TOP 10 only to the first query hoping that the analyzer will refer to the whole query, it's selecting TOP 10 from the first query and combines it with all the records from the others...

So, can anyone help? I hope the problem is understood.

Thank you,
Inon.

View 2 Replies View Related

Need Help With UNION And SUM

Nov 8, 2004

Hi!

I am trying to join to different queries into one table ( I accomplished this)

Next I need to ADD or SUM the results of 2 rows to form a single row.

As you can see in the query below, I run 2 separate queries and use ' ' as a place holder for the UNION to work. I get duplicate rows, one with a value and the other with a '0'. I want to have a single row.

Any help is greatly appreciated!

(SELECT
v_gs_supportedpackages.ProdID0 as 'Product Name',
v_RA_System_SMSInstalledSites.SMS_Installed_Sites0 as 'Site',
Count(ProdID0) as '# copies installed',
'' as '# legitimate copies installed'

FROM
v_R_System SYS,
v_GS_Workstation_Status HWSCAN,
v_gs_SupportedPackages
inner join
v_RA_System_SMSInstalledSites on v_RA_System_SMSInstalledSites.ResourceID = v_gs_SupportedPackages.ResourceID

WHERE
SYS.ResourceId = HWSCAN.ResourceId
AND
SYS.ResourceId = v_gs_SupportedPackages.ResourceId
AND
v_gs_supportedpackages.ProdID0 = substring('MS Security Patch MS04-030,031,032,034,037,038',1,60)

GROUP BY

v_gs_supportedpackages.ProdID0,
v_RA_System_SMSInstalledSites.SMS_Installed_Sites0 )

UNION

(SELECT
v_gs_supportedpackages.ProdID0 as 'Product Name',
v_RA_System_SMSInstalledSites.SMS_Installed_Sites0 as 'Site',
'' as '# copies installed',
Count(ProdID0) as '# legitimate copies installed'

FROM
v_R_System SYS,
v_GS_Workstation_Status HWSCAN,
v_gs_SupportedPackages
inner join
v_RA_System_SMSInstalledSites on v_RA_System_SMSInstalledSites.ResourceID = v_gs_SupportedPackages.ResourceID

WHERE
SYS.ResourceId = HWSCAN.ResourceId
AND
SYS.ResourceId = v_gs_SupportedPackages.ResourceId
AND
v_gs_supportedpackages.ProdID0 = substring('MS Security Patch MS04-030,031,032,034,037,038',1,60)
AND
DateDiff(Day,HWSCAN.LastHWScan,GetDate()) <= '20'

GROUP BY

v_gs_supportedpackages.ProdID0,
v_RA_System_SMSInstalledSites.SMS_Installed_Sites0 )

View 4 Replies View Related

UNION Vs UNION ALL

Dec 15, 2006

Just a pet peeve....

From BOL:


By default, the UNION operator removes duplicate rows from the result set.
If you use ALL, all rows are included in the results and duplicates are not
removed.



Why is it assumed that one would want the duplicates removed by default?
Isn't that what SELECT DISINCT is for?

View 1 Replies View Related

Union

Mar 18, 2004

I was wondering if there is a way I could write this query as one query using union instead of 3 different queries?

SELECT columna as column,count(ID) as Applications
FROM tablea
GROUP columna


SELECT columna as column2, count(ID) as Approved
FROM tablesa where substring(APP_DATE,1,6) >'200304' and in 'Approved')
GROUP BY columna

SELECT columna as column3, count(ID) as Booked, sum(AMT) as amt, sum(AMT)/count(ID) as lavg
from tablea where substring(APP_DATE,1,6) >'200304' and STATUS in('book')
group by columna

View 2 Replies View Related

UNION Vs UNION ALL

Apr 22, 2008

Hi All,

I am doing a UNION of 8 views(with 3 million rows in each view approx). I am sure that there is NO DUPLICATE data between the views. SO specifying a UNION ALL would be better than specifying a UNION in this case?

Thanks!

Prakash.P
The secret to creativity is knowing how to hide your sources!

View 8 Replies View Related

Union

Jun 20, 2008

Hi folks,
I have a question about the performance of the following query:

(SELECT name, lastName
from Data
where salary >= 2200
)
union

(SELECT name, lastName
from Data
where salary >= 2200
) union

(SELECT name, lastName
from Data
where salary >= 2200
)

How that query works? Is it slow for a larger data? Does it do 3 queries and then combine them?

Thank you.

View 3 Replies View Related

Union

Feb 11, 2008

Hi All
I want to do the following report and I want the 2nd select statement to appear at the bottom to display the totals


SELECT Item,B.Mar, B.Apr, B.May, B.Jun,B.Jul, B.Aug, B.Sep, B.Oct, B.Nov, B.Dec, B.Jan
FROM fncWineSales(2007) AS A
UNION
SELECT 'Total',SUM(B.Mar), SUM(B.Apr), SUM(B.May), SUM(B.Jun),SUM(B.Jul), SUM(B.Aug), SUM(B.Sep), SUM(B.Oct), SUM(B.Nov), SUM(B.Dec), SUM(B.Jan)
FROM dbo.fncWineSales (2007) AS B


thanx in advance

View 11 Replies View Related

Other Than UNION

Jul 23, 2005

Hello,Bear with me (not had much sleep last night), pls see following ddl,dml and comments for what is desired, I don't have a problem gettingdesired result(s), however, I'm wondering if there's another (better)solution than UNION operator in this case. TIA.-- DDLCREATE TABLE #TMP (col varchar(10));-- DMLinsert into #TMPvalues('A124');insert into #TMPvalues('A127');insert into #TMPvalues('A12728');insert into #TMPvalues('A17282');insert into #TMPvalues('BCD');insert into #TMPvalues('BCD');insert into #TMPvalues('CDSS');insert into #TMPvalues('DS');insert into #TMPvalues('YUUEI');-- goal: get one row with col data starting with 'A' and distict rowsfor the restselect top 1 colfrom #TMPwhere col LIKE 'A%'UNIONselect distinct colfrom #TMPwhere col NOT LIKE 'A%'

View 4 Replies View Related

Sum A Union

Jul 20, 2005

Hi,I can return results of a union easily enough, but I wish to sum the recordsfirst. Is this possible in a single SQL statement? Or do I have to useseparate ones to sum up after the union?Cheers,Chris

View 2 Replies View Related

T-SQL UNION With Sum

Aug 20, 2007

Why if run this script, Always Incorrect syntax near the keyword 'group'.
somebody helpme, how to sum the union script

SELECT NUMMSTR3, sum(qty), sum (AP)
FROM (

SELECT c.NUMMSTR3, count(*) qty,sum(a.DEBIT) DEBIT,sum(a.CRDT) CRDT,Sum(a.CRDT - a.DEBIT) AP

FROM MSTR1 a,MSTR2 b,MSTR3 c

where a.IDMSTR1 = b.IDMSTR1

and a.KDMSTR1 = c.KDMSTR3

and c.NUMMSTR3 = '21010100'

and year(b.TRXDATE) <= '2007'

group by c.NUMMSTR3
UNION ALL

SELECT c.NUMMSTR3, count(*) jml,sum(a.DEBIT) DEBIT,sum(a.CRDT) CRDT,Sum(a.CRDT - a.DEBIT) AP

FROM HISTR4 d,MSTR3 c

where d.KDHISTR4 = c.KDMSTR3

and c.NUMMSTR3 = '21010100'

group by c.NUMMSTR3
) group by NUMMSTR3

View 5 Replies View Related

UNION

Jul 5, 2006

Of the two queries below, the first returns the desired result, the union of the select-except statments.  Why doesn't the second query return the same result as the first? Is this a mistake/bug on Microsofts side?

 

select * from

(select * from TEST1

EXCEPT

select * from TEST2) AS A

UNION

select * from (select * from TEST2

EXCEPT

select * from TEST1) AS B

 

 

 

select * from TEST1

EXCEPT

select * from TEST2

UNION

select * from TEST2

EXCEPT

select * from TEST1

View 3 Replies View Related

Union All

Sep 3, 2006

When any changes have been made to the underlying table structure, the Union all reports error and does not automatically correct the error. I then have to delete it and recreate it. Is this a bug?

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved