UNION OF 2 TABLES - CAN'T GET SUM
Sep 7, 2005
I am trying to get a consolidated sum of all the columns of the two tables that I am Using the UNION on. I can not get the sum function to work or the group by.
SELECT T2.State,t2.Taxcode,
Taxable =
Case
When T1.TaxCode <> 'exempt' and T1.TaxStatus = 'Y' then T1.LineTotal - ((T0.DiscPrcnt/100) * T1.LineTotal)
Else 0
End,
'NonTaxable' =
Case
When T1.TaxCode = 'exempt' or T1.TaxStatus = 'N' then T1.LineTotal + T1.DistribSum - ((T0.DiscPrcnt/100) * T1.LineTotal)
Else T1.DistribSum
End,
T1.VatSum as 'Total Tax', (T1.LineTotal + T1.DistribSum - ((T0.DiscPrcnt/100) * T1.LineTotal) + T1.Vatsum) as 'Line Total'
FROM inv1 t1 inner JOIN oinv t0 ON T0.DocEntry = T1.DocEntry inner join CRD1 T2 on T0.Cardcode = T2.CardCode
WHERE T0.DocDate >='[%1]' AND T0.DocDate <='[%2]' and t2.address = T0.shiptocode and t2.adrestype = 's'
UNION ALL
SELECT t2.state,t2.taxcode,
Taxable =
Case
When T1.TaxCode <> 'exempt' and T1.TaxStatus = 'Y' then -(T1.LineTotal - ((T0.DiscPrcnt/100) * T1.LineTotal))
Else 0
End,
'NonTaxable' =
Case
When T1.TaxCode = 'exempt' or T1.TaxStatus = 'N' then -(T1.LineTotal + T1.DistribSum - ((T0.DiscPrcnt/100) * T1.LineTotal))
Else T1.DistribSum
End,
-T1.VatSum as 'Total Tax', -(T1.LineTotal + T1.DistribSum - ((T0.DiscPrcnt/100) * T1.LineTotal) + T1.Vatsum) as 'Line Total'
FROM RIN1 t1 inner JOIN ORIN t0 ON T0.DocEntry = T1.DocEntry inner join CRD1 T2 on T0.Cardcode = T2.CardCode
WHERE T0.DocDate >='[%1]' AND T0.DocDate <='[%2]' and t2.address = T0.shiptocode and t2.adrestype = 's'
order by T2.STATE
View 2 Replies
ADVERTISEMENT
Sep 17, 2007
Hi,
I have three tables named as BroadCastetails,PaymetMaster,MemberMaster.
I have two query as mentioned below--
SELECT MemberMaster.FirstName, MemberMaster.LastName, BroadCastDetails.BroadCastName FROM BroadCastDetails INNER JOIN MemberMaster ON 5 = MemberMaster.MemberID AND BroadcastCreationDateTime between '01/01/2007' AND '12/12/2007'
SELECT BroadCastDetails.ScheduledStartDateTime, BroadCastDetails.TotalCalls FROM BroadCastDetails UNION SELECT PaymentTransaction.TransactionDate, PaymentTransaction.CallsToCredit FROM PaymentTransaction
I want the result of the above two query in a GridView.How can I do that ? Its urgent...
View 2 Replies
View Related
Nov 18, 2003
Hi,
I have an Orders Table and a Freeshipping Table.
ORDERS
======
CustomerId OrderDate Quantity
========== ========= ========
1000 01/01/2003 5
1000 01/04/2003 9
1000 01/08/2003 14
2000 01/01/2003 4
1000 06/03/2003 9
4000 05/02/2003 4
FREESHIPPING
=============
CustomerID FreeDate
========== ========
1000 01/01/2003
1000 01/03/2003
How can I write a query that will return the following result to show the order details for customerid 1000
for a date range between 01/01/2003 and 01/08/2003
OrderDate Quantity FreeShipping
========= ======== ============
01/01/2003 5 Y
01/03/2003 0 Y
01/04/2003 9 N
01/08/2003 14 N
Note that even if an order was not placed and the date was a freeshipping date, it is still displayed in the resultset
Thanks in advance,
-ron
View 2 Replies
View Related
Mar 27, 2006
I have 2 Tables one called Reps and the other called STORES
I need to use a UNION statement but my Server is saying that the "UNION" is ERROR The Query Designer does not support the UNION SQL construct.
What command would I use to put these 2 tables together?
HELP!
View 5 Replies
View Related
Nov 8, 2007
I have 2 temporary tables from a previous operation, Tab1 and Tab2, with the same dimensions. How do I create a third table Tab3 with the same dimensions containing the the combined rows of the 2 previous tables? TIA!
Tab1
Col1 Col2 Col3
A1 B1 C1
A2 B2 C2
Tab2
Col1 Col2 Col3
X1 Y1 Z1
X2 Y2 Z2
X3 Y3 Z3
After the required sql operation I should have
Tab3
Col1 Col2 Col3
A1 B1 C1
A2 B2 C2
X1 Y1 Z1
X2 Y2 Z2
X3 Y3 Z3
View 7 Replies
View Related
Feb 19, 2008
Hi there, I have some identical tables that I want to query for a search Is there anyway I can execute the unions first then a where command on all the tables at once I have tried using go but it doesn't seem to work, so I put the where statemtents at the end of each union for now. Here's my code:
strSQL = "SELECT * FROM england WHERE company LIKE '%" & iKeyword & "%' OR address1 LIKE '%" & iKeyword & "%' OR address2 LIKE '%" & iKeyword & "%' OR address3 LIKE '%" & iKeyword & "%' OR address4 LIKE '%" & iKeyword & "%' OR address5 LIKE '%" & iKeyword & "%' OR postcode LIKE '%" & iKeyword & "%' " &_
"UNION ALL SELECT * FROM ni WHERE company LIKE '%" & iKeyword & "%' OR address1 LIKE '%" & iKeyword & "%' OR address2 LIKE '%" & iKeyword & "%' OR address3 LIKE '%" & iKeyword & "%' OR address4 LIKE '%" & iKeyword & "%' OR address5 LIKE '%" & iKeyword & "%' OR postcode LIKE '%" & iKeyword & "%' " &_
"UNION ALL SELECT * FROM wales WHERE company LIKE '%" & iKeyword & "%' OR address1 LIKE '%" & iKeyword & "%' OR address2 LIKE '%" & iKeyword & "%' OR address3 LIKE '%" & iKeyword & "%' OR address4 LIKE '%" & iKeyword & "%' OR address5 LIKE '%" & iKeyword & "%' OR postcode LIKE '%" & iKeyword & "%' " &_
"UNION ALL SELECT * FROM scotland WHERE company LIKE '%" & iKeyword & "%' OR address1 LIKE '%" & iKeyword & "%' OR address2 LIKE '%" & iKeyword & "%' OR address3 LIKE '%" & iKeyword & "%' OR address4 LIKE '%" & iKeyword & "%' OR address5 LIKE '%" & iKeyword & "%' OR postcode LIKE '%" & iKeyword & "%'"
View 15 Replies
View Related
Feb 10, 2015
This query works perfectly and orders by just as I need
Code:
Select
'1st' As [Type],
#Uno.ID
#Uno.Address,
#Uno.shippingInfo
FROM #Uno
[Code] ....
However, when I use it in a Union All so I can pull data from 2 diff tables, the order by statement no longer works. How can I order by data in 2 tables?
Code:
Select
'1st' As [Type],
#Uno.ID
#Uno.Address,
#Uno.shippingInfo
FROM #Uno
[Code] ....
View 1 Replies
View Related
Oct 4, 2013
I try to build some query on hierarchy data and after two days thinking about it i have null result. What I need is union more trees tables by root id to one table.
-- tree
-- this query is functional: OK
WITH tree (sid, parend_id, level) as
(
SELECT sid, parend_id, 0 as level
[Code] ....
But this query work with one root id (100); what can i do when i have more roots id ? -> generate each tree table separated by roots id and then all tables join to one (union).
View 1 Replies
View Related
Aug 5, 2007
Is it possible to combine the following tables:
Table1:
id
1
2
3
Table2:
licenses
3
2
1
To one table, like this:
id licenses
1 3
2 2
3 1
-There is always the same number of rows in both tables.
Thanks a lot!
View 9 Replies
View Related
Oct 4, 2007
Hello,
I'm using SQL2005, SP2
I have multiple temp tables with the same column structure that I would combine into into a single temp table using Unions.
Is this possible?
Example:
Select * From #temp1
Union All
Select * From #temp2
Union All
Select * From #temp3
Into #Temp4
I thought I would ask while I continue to research this in case someone came back with the solution before I was able to track it down elsewhere.
Thanks!
View 1 Replies
View Related
Oct 17, 2007
What is the standard way to union tables with exactly the same schema that are in different databases?
For example:
Code Block
USE db1 SELECT * FROM table1
UNION
USE db2 SELECT * FROM table2
this will return the following error:
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'use'.
View 3 Replies
View Related
May 24, 2006
I have 2 tables
tblOpenSiteDates
Site: int
OpenDate: smalldate
Comment: VarChar
tblCloseSiteDates
Site: int
CloseDate: smalldate
Comments: VarChar
newLocationID: int
I am trying to get a view which would display a site with the open and close dates. Null is ok for a close date for those opens that are still open.. not every office has an open date and close date.. it is possible to have just a close date and not an open date (ie unsure of open date but I know its closing)
so the output would be
viewOpenCloseSites
Site: int
opendate: smalldate
closedate: smalldate
PLEASE HELP i just can not figure it out
thanks...
View 1 Replies
View Related
Jul 16, 2012
I have two tables with data that I need to get and display in a combobox. What I want to do is have the parent table listed in the combobox with all of its children indented. Sorted by parent then by child.
This SQL seems to be more complex than I have done previously
I can get all of the records from both tables easily:
Code:
SELECT strName, ID FROM tblParents as pp
INNER JOIN tblChildren as cc
ON cc.pID = pp.uiGUID
UNION (SELECT strName FROM tblChildren as c
INNER JOIN tblParents as p
ON c.pID = p.ID)
However, this simply returns a list that is not ordered in any fashion. I'd like to have all of the parent's children shown under the parent name (there is only 1 parent per child and multiple children per parent)
View 2 Replies
View Related
Oct 25, 2007
Hi!
I'm trying to get the results from three different tables, where they have some of the same results. I'm only interested in where they match and then trying to order by date (that's in three columns - M, D, Y). I read previous post in 9/07 but the result doesn't seem to order correctly. It does not have any rhyme or reason to the outputed results as it bounces back and forth through Oct, Nov and Dec posting and throughout all three tables. Here's my query below. Any ideas how I can get my ordering correct for all three tables to display all Oct, all Nov and all Dec?
Thanks so much
select date3, date2, date1, who, what
from
(
select date3, date2, date1, who, what from shows
union
select date3, date2, date1, who, what from shares
union
select date3, date2, date1, who, what from soiree
)
a order by date3, date2, date1
View 4 Replies
View Related
Apr 2, 2008
I have got the following union statement:
SELECT plan2008.jahr, plan2008.monat, plan2008.kdkrbez, plan2008.kdgrbez, plan2008.abgrbez, plan2008.artnr,
FROM plan2008
GROUP BY plan2008.jahr, plan2008.monat, plan2008.kdkrbez, plan2008.kdgrbez, plan2008.abgrbez, plan2008.artnr
UNION
SELECT fsp_auftrag.jahr, fsp_auftrag.monatnr, fsp_auftrag.kundenkreis, fsp_auftrag.kundengruppe, fsp_auftrag.abnehmergruppe, fsp_auftrag.artnr
FROM fsp_auftrag
GROUP BY fsp_auftrag.jahr, fsp_auftrag.monatnr, fsp_auftrag.kundenkreis, fsp_auftrag.kundengruppe, fsp_auftrag.abnehmergruppe, fsp_auftrag.artnr
My problem is that each table contains additional values like art_amount, art_turnover etc... whereby the first table contains plan values while the second table contains actual values.
My goal is to get plan as well as the actual values in one row, how is that possible? If I put the values into each of the selects I get two rows, which is not the wished output.
Is it possible to join the tables after the union took place?
Thanks in advance!
View 8 Replies
View Related
Apr 1, 2008
HI, Guys:
I've some AT_DATE tables (eg: AT_20080401, AT_20080402, ...) in SQLServer DB, and these AT_XX table have same columns. but table count could be variant, so I have to query sysobjects to get all of these tables. like this:
select name from sysobject where name like 'AT_%'
Now I try to create a view AT which is the union of all these AT_XX tables, such as:
Code Snippet
Create View AT as
select * from AT_20080401
union
select * from AT_20080402
union ...
but since I'm not sure how many tables there, it would be impossible to write SQL as above.
though I could get this union result via stored-procedure, view couldn't be created on the resultset of a procedure.
Create View AT as
select * from AT_createView() <-- AT_createView must be a function, not procedure
I've checked msdn, there is Multi-statement table-valued function, but this function type seems to create one temporary table, I don't want to involve much of insert operation because there could be more than 1million records totally in these AT_XX tables.
So is there any way to achived my goal?
any reference would be appreciated, thanks !
View 8 Replies
View Related
Feb 18, 2008
I'm trying to summarize costs assigned to active jobs for a manufacturing business. I need to aggregate work in process (WIP) cost that resides in labor-transaction and part-transaction tables based on transaction types, and transaction dates. Some transactions increase the WIP cost of the job while others decrease WIP. The business needs to see how much $$ is tied up in each job as of a particular date -- the calculation is:
ToDate (cost of materials and labor assigned to job)
- ToInv (cost of materials returned to inventory)
- ToSales (cost of materials sold).
I developed this query incrementally and, so far, the #ToDate, #ToInv, and #ToSales temp tables seem to be populating with the correct data. My thought was to combine these three tables with a UNION and then extract the grand totals and here's where I started getting the following error:
------------------------------------------
Incorrect syntax near the keyword 'UNION'.
------------------------------------------
The problem is with the UNIONs going into #myTotal.
I would appreciate any help with this. Also, please let me know if you can suggest a better design for this. Thanks!
Below is a simplified version of my query:
--#ToDate
CREATE TABLE #ToDate (JobNum varchar(14), Cost decimal (16,2)
INSERT INTO #ToDate (JobNum, Cost)
--M&S To Date
SELECT pt.jobnum,
SUM(pt.extcost) AS Cost
FROM parttran pt
JOIN jobhead jh ON pt.jobnum=jh.jobnum
WHERE trantype IN ( <valid trans types> )
AND jh.JobReleased = 1
AND pt.TranDate < '2007-9-30'
GROUP BY pt.jobnum
UNION -- This one works ok.
--L&B To Date
SELECT jh.JobNum,
sum(l.LaborRate*l.LaborHrs) + sum(l.BurdenRate*l.BurdenHrs) AS Cost
FROM LaborDtl l
JOIN JobHead jh ON l.JobNum = jh.JobNum
WHERE jh.JobReleased = 1
AND l.PayrollDate < '2007-9-30'
GROUP BY jh.JobNum
--#ToInv
CREATE TABLE #ToInv (JobNum varchar(14), Cost decimal (16,2)
INSERT INTO #ToInv (JobNum, Cost)
SELECT pt.jobnum,
SUM(pt.extcost) AS ToInv
FROM parttran pt
JOIN jobhead jh ON pt.jobnum=jh.jobnum
WHERE trantype IN (<valid trans types>)
AND jh.JobReleased = 1
AND pt.TranDate < '2007-9-30'
GROUP BY pt.jobnum
--#ToSales
CREATE TABLE #ToSales (JobNum varchar(14), Cost decimal (16,2))
INSERT INTO #ToSales (JobNum, Cost)
SELECT pt.jobnum,
SUM(pt.extcost) AS ToInv
FROM parttran pt
JOIN jobhead jh ON pt.jobnum=jh.jobnum
WHERE trantype IN (<valid trans types>)
AND jh.JobReleased = 1
AND pt.TranDate < '2007-9-30'
GROUP BY pt.jobnum
--#myTotal
CREATE TABLE #myTotal (JobNum varchar(14), Cost decimal (16,2), Source varchar(9))
INSERT INTO #myTotal (JobNum, Cost, Source)
SELECT d.JobNum, SUM(d.Cost) AS Cost FROM #ToDate d GROUP BY d.JobNum ORDER BY d.JobNum
UNION -- Problem**********************
SELECT i.JobNum, SUM(-1*i.Cost) AS Cost FROM #ToInv i GROUP BY i.JobNum ORDER BY i.JobNum
UNION -- Problem**********************
SELECT s.JobNum, SUM(-1*s.Cost) AS Cost FROM #ToSales s GROUP BY s.JobNum ORDER BY s.JobNum
--Select grand total for each job
SELECT JobNum, SUM(Cost) FROM #myTotal ORDER BY JobNum
--Drop temp tables
DROP TABLE #ToDate
DROP TABLE #ToInv
DROP TABLE #ToSales
DROP TABLE #myTotal
View 3 Replies
View Related
Aug 18, 2014
Currently I have a standard query with a join to several tables. There are two additional tables MAS_CTB and MAS_STB. I would like to do a union between those two tables to get FIELDVALUE which will exist in either the CTB table or the STB table and then have that value be returned with the results of the original query.
I can of course write a UNION from the main query to the CTB and then to the STB table, however it's about 80 dummy fields I would have to replicate in the union which is why I was wondering if there was a more simple way.
Main Query:
SELECT Field1, Field2...Field80
From Table1
Join Table2
Union Portion:
Select FieldValue <------
From MAS_CTB
UNION
Select FieldValue <-----Return to main query above
From MAS_STB
View 3 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
