.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
Imran Ghani
Post New Web Links

T-SQL UNION with sum

Posted By:      Posted Date: September 20, 2010    Points: 0   Category :Sql Server
 
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 Complete Post


More Related Resource Links

Need Syntax To Make Results of LINQ Union Return Non-Generic Type

  

Hi.

I have the below SQL which works just fine:

SELECT     Message, CreateDate, AccountId, AlertTypeId
FROM       dbo.Alerts
UNION
SELECT     TOP (100) PERCENT Status, CreateDate, AccountId,
                          (SELECT     10 AS Expr1) AS AlertTypeId
FROM         dbo.StatusUpdates
WHERE AccountId = PassedInParameter
ORDER BY CreateDate DESC

I have been going about trying to convert the above into a LINQ method and am having problems with return types.
As the above SQL illustrates, I am querying two distinct LINQ tables - Alerts and StatusUpdates -
and then attempting to perform a Union on them. In order to do the union in LINQ, I have to have agreeing types. So, I
am using "var" to make the results of "alerts" and "updates" generic before the union.  

public IList GetStatusUpdatesAsAlertsByAccountId<T>(Int32 accountId)
        {
            using (WorkbookDataContext dc = _conn.GetCont

order by in combination with union

  

 Hi All,

How can I order this by count? so if it appears above and below the union statement, I want it on top

	SELECT distinct BIER.Naam, BIER_L2.L2_Naam, BIER_L3.L3_Naam from BIER_MATCH, BIER, BIER_L2, BIER_L3
	where BIER_L2.L2_ID = BIER.L2_ID and BIER_L2.L3_ID = BIER_L3.L3_ID and BIER.ID = BIER_MATCH.BIER_ID 
	and RECEPT_ID = @RECEPT_ID 

	
	UNION  
	
	--L2
	SELECT distinct BIER.Naam, BIER_L2.L2_Naam, BIER_L3.L3_Naam from BIER_MATCH, BIER, BIER_L2, BIER_L3
	where BIER_L2.L2_ID = BIER.L2_ID and BIER_L2.L3_ID = BIER_L3.L3_ID and RECEPT_ID = @RECEPT_ID 
	and BIER_MATCH.BIER_ID is null


 


How to union two queries without duplicates

  


I have a sql query that returns 4 columns
CustName CustId CustZip CustPhone

I have a second sql query that returns the following 5 columns

CustName CustId CustZip CustEmail CustAddress

Both queries, query different data tables in the database, but return columns that are common to
both.

How do I union the two queries(Assuming a union is needed)

Which will result in no duplicates and an end result being the following output:

CustName CustId CustZip CustPhone CustEmail CustAddress

 

As you can see we want to not have duplicate values on output. So something like the following
is not acceptable:

Jeff Stamper  2222234  81224  498-300-2222
Jeff Stamper  2222234  81224  498-300-2222 js@jj.com 122 Mars Blvd
Karen Bops  3322234    81666  498-300-2222
Karen Bops  3322234    81666  498-300-2222 kb@lpo.com 322 Jamer Road


Linq Query Union

  
HelloI need to use Union for below Linq queries. I could use Union it if it was simple "select new whith{" but Now that I Specified Class, I encounter Error using unionDim CStep() As New List(Of ProductModel) CStep= (From p In db.Produts _ Where p.CategoryID= 1 _ Select New ProductModel() With { _ .ID = p.ProductID, _ .Name = p.Title, _ } Take (5)).Union (From p In db.Produts _ Where p.CategoryID= 2 _ Select New ProductModel() With { _ .ID = p.ProductID, _ .Name = p.Title, _ } Take (5)).ToList()

UNION hints

  
Are there any UNION hints that would allow for a UNION to determine uniqueness based only a specific columns instead of all columns?   I specifically need UNION instead of UNION ALL however one entity is showing up in both datasets however counts are different making two records when in reality I just want the first.

Multiple Order by clauses with Union

  

I'm trying to figure out how to run this code, I have over 40 tables that I need to get data from, the only thing I can think of is to use "Union" , I need to get the last record in each table by StartTS (StartTimeStamp) so I figured the best approach is to use "Select Top (1) and use Order by StartTS to get the data.  This works great if you are only grabbing data from 1 table, however like I said I need to grab from over 40. 

My example uses only 2 tables:

SELECT   TOP (1)
             (SELECT   [Unit Name]
              FROM     dbo.UnitVerificationData
              WHERE   (Unit_Code = dbo.CP_BCELP.Unit_Code)) AS UnitName, Operation_type, StartTS, StopTS
FROM     dbo.CP_BCELP
ORDER BY StartTS DESC
UNION
SELECT   TOP (1)
             (SELECT   [Unit Name]
              FROM     dbo.UnitVer

Union on columns

  

Could you please help me

with Vertical union, I have 4 different datasets which is to be combined as one.

How do I accomplish this task?

Thanks

&

order by sort both select in union

  

Hi

I have DropDownList that Show all groups. in that, I want to show "select one group" as default in DDL. for that i used code below. but it sort all return data and "select one group" will not be the first line. How can i fix it?

Best Regards. Morteza

 

	SELECT   -1 as id,'select one group' as groupName
	union
	SELECT   id, groupName
	FROM     [group] 
	order by groupNam

Help :Is this Query right? How to Get the Result: Which to use union OR join AND How?

  
Can you please help me?

QUERY1:

SELECT COUNT(T1.ID) AS T1_COUNT,convert(date,DATEADD(dd, 7-(DATEPART(dw, T1.TIME)), T1.TIME),101) AS T1_TIME FROM TABLE_1 AS T1,TABLE_2 AS T2 WHERE T2.ID=T1.ID AND T1.STATE != 'C' AND T1.TIME BETWEEN '2010-01-01 00:00:00' AND '2010-09-25 23:59:59' AND PRTY='F' GROUP BY convert(date,DATEADD(dd, 7-(DATEPART(dw, T1.TIME)), T1.TIME),101) ORDER BY T1_TIME DESC

GETTING RESULT:
T1_COUNT ---------- T1_TIME
3 ---------------- 2010-09-25
4 ---------------- 2010-09-18
2 ---------------- 2010-09-11

QUERY2:

SELECT COUNT(T1.ID) AS T2_COUNT,convert(date,DATEADD(dd, 7-(DATEPART(dw,T1.TIME)), T1.TIME),101) AS T2_TIME FROM TABLE_1 AS T1,TABLE_2 AS T2 WHERE T2.ID=T1.ID AND T1.STATE != 'C' AND T1.STATE ='E' AND T1.TIME BETWEEN '2010-01-01 00:00:00' AND '2010-09-25 23:59:59' AND PRTY='F' GROUP BY convert(date,DATEADD(dd, 7-(DATEPART(dw, T1.TIME)), T1.TIME),101) ORDER BY T2_TIME DESC

GETTING RESULT:
T2_COUNT ---------------- T2_TIME
1 ---------------- 2010-09-25
2 ---------------- 2010-09-18

Sample Query:
SELECT * FROM (
SELECT COUNT(T1.ID) AS T1_COUNT,convert(date,DATEADD(dd, 7-(DATEPART(dw, T1.TIME)), T1.TIME),101) AS T1_TIME FROM TABLE_1 AS T1,TABLE_2 AS T2

Can I Combine resultset return by union

  

Hi,

 

Is there a way to row two and three of the result set be returned so that the

 

following query:

 

Select Distinct A as TotalUniqueTranTypeT, B as TotalUniqueTranTypeO, C as Week from

(

SELECT     COUNT(DISTINCT EmailTracking.EmailAddress) AS B, 0 as A, DATEPART(Week, EmailTrackingDetail.TranDate) AS C

FROM         EmailTrackingDetail INNER JOIN

                      EmailTracking ON EmailTrackingDetail.EmailTrackingRecordID = EmailTracking.RecordID

WHERE     (EmailTrackingDetail.TranType = 'O')

GROUP BY DATEPART(Week, EmailTrackingDetail.TranDate)

union

SELECT     0 AS B, COUNT(DISTINCT EmailTracking.EmailAddress) AS A, DATEPART(Week, EmailTrackingDetail.TranDate) AS C

FROM   EmailTrackingDetail INNER JOIN

                      EmailTracking ON EmailTrackingDetail.EmailTrackingRecordID = EmailTracking.RecordID

WHERE   &n

How to union two MDX queries

  

Hi,

Iam doing reports using MDX queries,i need to do union of these two MDX queries,"[PA to ID SUM]","[PD to AI SUM]" these are statuses and also parameters for  report,actually i thought to display data in seperate table for each of this MDX query,but if i select "PA to ID SUM" , the table used to display"PD to AI SUM" shows error.I tried to hide table in the report ,but the status "PA to ID SUM","PD to AI SUM" like that there are 40 statuses,for 40 statuses i need to diplay 40 tables.i think hiding the table is not a good idea ,if there is any possibility to do union of these two mdx queries,i can display all data in one table

Actually the requirement is , 40 status are there for each orderid,status is nothing but no of minutes taken by the orderid to complete that status, i need to get min ,max ,avg,median ,percentile for  each status

WITH
MEMBER Measures.[PAtoID_MIN] as MIN([DimOrder].[Order Id].[Order Id],[Measures].[PA to ID SUM])
MEMBER Measures.[PAtoID_MAX] as MAX([DimOrder].[Order Id].[Order Id],[Measures].[PA to ID SUM])
MEMBER Measures.[PA75] as MIN(TOPPERCENT([DimOrder].[Order Id].[Order Id],75,[Measures].[PA to ID SUM]),[Measures].[PA to ID SUM])
MEMBER Measures.[PA80] as MIN(TOPPERCENT([DimOrder].[Order Id].[Order Id],80,[Measures].[PA to ID SUM]

mdx union with custom member value

  

Dear All,

Consider this simple mdx query

SET

 

[CustomCountry] AS

{[Clients].[Country].&[USA],

[Clients].[Country].&[Canada]}

Select

 

{[Measures].[Sales ]}

ON COLUMNS,

 

Looping vs Union/ Exists - How would I do this task?

  

Hello,

I have an engineering system writing alarms to a SQL server 2000 table. I would like to process the table at night and store the results in a new table.

It will look at all alarms in a 24 hour period and for each distinct record occurrence count the frequency for the alarm for that 24 hour period and for the preceding period of 8-90 days. Store the results in a table. (I’m looking for chronic alarms)

Resulting in something like this for rows of the stored table:

Alarm      Date         Count 24hr_period                     Count 8-90day_period

Tank1

MSXML2 or XmlDocument ? using an union Expression in XPath 2

  


Hallo, I'm using XPath for a long time by MSXML, now I tested some xml access with XmlDocument and have a problem with using XPath2.
 

This one works:

DOMDocument doc2 = new MSXML2.DOMDocument();
MSXML2.doc2.loadXML(xmlText);
MSXML2.IXMLDOMNode node = doc2.selectSingleNode("bla1/bla2/(ytype|xtype)");



This one works not:

XmlDocument xmlDoc = new XmlDocument();
xmlDoc.LoadXml(xmlText);
XmlNode node = xmlDoc.SelectSingleNode("bla1/bla2/(ytype|xtype)");


Is there any explanation? Which class should I use in .NET for XPath 2 XML access ?

Best regards

Roy


MSXML2 or XmlDocument ? using an union Expression in XPath 2

  


Hallo, I'm using XPath for a long time by MSXML, now I tested some xml access with XmlDocument and have a problem with using XPath2.
 

This one works:

DOMDocument doc2 = new MSXML2.DOMDocument();
MSXML2.doc2.loadXML(xmlText);
MSXML2.IXMLDOMNode node = doc2.selectSingleNode("bla1/bla2/(ytype|xtype)");



This one works not:

XmlDocument xmlDoc = new XmlDocument();
xmlDoc.LoadXml(xmlText);
XmlNode node = xmlDoc.SelectSingleNode("bla1/bla2/(ytype|xtype)");


Is there any explanation? Which class should I use in .NET for XPath 2 XML access ?

Best regards

Roy


NULL for user who don't have appointment - UNION error ... LEFT JOIN doesn't work

  

Hi,

It's so silly what i'm trying to do.. I just need to catch the entire list of user and null for users who don't have appointment ....

I tried union but I've got an error, please help

 

Msg 205, Level 16, State 1, Line 2
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.




declare @SQL Varchar(4000)

declare @CRM_FilteredAppointment nvarchar(2000)
declare @CRM_FilteredSystemUser nvarchar(2000)



set @CRM_FilteredSystemUser = 'SELECT systemuserid, eu_reporthubname, fullname, businessunitidname 
 FROM FilteredSystemUser 
 WHERE businessunitidname = ''Spain''
'
--exec (@CRM_FilteredSystemUser)

set @CRM_FilteredAppointment = 'SELECT activityid, ownerid, regardingobjectid, subject, scheduledend 
 FROM FilteredAppointment 
'

set @SQL = ' 
 SELECT SU.systemuserid, SU.fullname, count(partyid)
 FROM (' + @CRM_FilteredSystemUser + ') AS SU
   LEFT JOIN
    (' + @CRM_FilteredAppointment + ') as Apt ON 
    SU.systemuserid = Apt.ownerid 
   INNER JOIN
    FilteredActivityParty AS AP ON Apt.activityid = AP.activityid 
   AND (AP.participationtypemask = 8 OR AP.participationtypemask = 5) 
 WHERE Partyidname is not null 
group by SU.systemuserid, SU.fullname
UNION ALL 
SELECT

how to use union parameters

  

i have some equipment which needs to be split to two types. i have

with set A as {equip1, equip2...}

set B as {equip11, equip12...}

and i need to join those two sets to something big called [Group]

select
{[Measures1],
 [Measures2]}
 on columns,
 non empty
[Group]
 *
   [Date].[Calendar].[Date].members
  on rows
from CUbe

what i wanted see in the result is


Group Date Measure1 Measure2
A 2010.10.01    3 2
B 2010.10.01 d df
A 2010.10.02 f g
B 2010.10.02
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend