Groupby Unions
May 6, 2004hi
I have MSSQL query that performs multiple UNION ,but I would like to perform a GROUPBY on the whole result set.
How Can i do this?
plz help...
bono
ADVERTISEMENT
Multiple Unions Where There Are No Results In First Set Causes All Following Unions To Return Nothing.
Mar 21, 2008
I have two tables one with current data and a second with the same design that holds purged history.
I was going to create view and then jsut use a where clause to filter both tables but I figured it would be faster if the where clause was passed into each query as opposed to the whoel view having to load and then be filtered.
Select PatientName, MemberId
FROM CLAIMS
WHERE MemberID = @MemberID
UNION
Select PatientName, MemberId
FROM CLAIMSHistory
WHERE MemberID = @MemberID
But it appears that if there are no records in the first statement that the whole thing fails. I tried the union all operator with out any luck either.
Now if had a view like this;
Create view vAllClaims as
Select PatientName, MemberId
FROM CLAIMS
UNION
Select PatientName, MemberId
FROM CLAIMSHistory
And said select * from vAllClaims where memberid = 12345 would the query optimizer put the build the where statement onto each subquery or pull all the data first and query it?
Max With Groupby
Apr 8, 2008i have one query:
SELECT MAX(salesid) AS salesid, max(salestitle) as salestitle
FROM saleshistory s where list = 'sales'
GROUP BY DATEADD(dd, DATEDIFF(dd, 0, salesdate), 0)
order by salestitle
i m getting more than 1000 rows.. in output
some of them are :
salesid salestitle
---------------------------------
11871/20/2005 9:45:54 AM
821622K8_KTBcalendar_pre1 9/19/2007 10:46:02 AM
824962K8_KTBcalendar_pre1 9/20/2007 3:29:44 PM
918572K8_KTBcalender_inst 10/16/2007 2:47:31 PM
175660AmerHeartMo_2008 2/22/2008 1:05:24 PM
179614AmerHeartMo_2008 2/26/2008 4:54:18 PM
2554American_Heart_Month_Feb2005
25335Cholesterol_Educ_Mo_2006_Rem_test
25186Cholesterol_Educ_Mo_2006_Rem_test
25282Cholesterol_Educ_Mo_2006_Rem_test
i want (desired)output like: -
11871/20/2005 9:45:54 AM
824962K8_KTBcalendar_pre1 9/20/2007 3:29:44 PM
918572K8_KTBcalender_inst 10/16/2007 2:47:31 PM
179614AmerHeartMo_2008 2/26/2008 4:54:18 PM
2554American_Heart_Month_Feb2005
25335Cholesterol_Educ_Mo_2006_Rem_test
i want max(salesid) among that same salestitle
when i m doing like:
SELECT MAX(salesid) AS salesid, max(salestitle) as salestitle
FROM saleshistory s where list = 'sales'
GROUP BY substring([salestitle],1,charindex(' ',[salestitle])-1)
i m getting an error
Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.
(0 row(s) affected)
can anybody help me to get correct output.
thanks.
Use Of Groupby And Get Max Val
Feb 10, 2007Respected Friends,
kindly solve my query.
Table Structrue:
Table Name : Custom_Table_SPPR_Master
Fields :
Sno int pk
SPPR_No int pk
Revison_No int pk
rev_Date date
prop_amt decimal
claim_amt decimal
avail_amt decimal
Status int
Data
Sno SPPR_No Revison_No rev_Date prop_amtclaim_amtavail_amtStatus
1 2000 0 2006-10-01 00:00:00.000 1000 600 400 2
2 2000 1 2006-10-01 00:00:00.000 1100 600 500 2
3 2000 2 2006-10-01 00:00:00.000 1400 600 800 2
4 2000 3 2006-10-01 00:00:00.000 1500 900 600 2
5 2001 0 2006-10-01 00:00:00.000 1000 200 800 2
6 2001 1 2006-10-01 00:00:00.000 1200 600 600 2
7 2002 0 2006-10-01 00:00:00.000 1000 600 400 2
8 2002 1 2006-10-01 00:00:00.000 1100 600 500 2
9 2003 0 2006-10-01 00:00:00.000 1000 600 400 2
I want the record where max of the revision no is seen for each sppr
that is the result should the rows of sno: 4,6,8,9
i tried the following but in vein
select max(revision_no) ,sppr_no from Custom_Table_SPPR_Master group by sppr_no
-- in this i am getting records, but i want * datas
select distinct(sppr_no) from Custom_Table_SPPR_Master where revision_no in(select max(revision_no) from Custom_Table_SPPR_Master group by revision_no) group by sppr_no
select sppr_no,count(revision_no) from Custom_Table_SPPR_Master where revision_no in(select max(revision_no) from Custom_Table_SPPR_Master group by revision_no)
group by sppr_no,revision_no
Kindly let me the answers . thanks in advance.
Using MAX And GROUPBY
Apr 23, 2007Hi,
I need help in creating a query using MAX and group by.
The input SQL table ClientInfo contains data like the following:
Client Div MaxDt
00003 00 19820501
00003 00 19830208
00003 00 19850801
00003 00 20040811
00003 50 19850101
00003 50 20040811
00003 99 19870131
I need the output to have the max date for every division of every client and output should be as follows:
Client Div MaxDt
00003 00 20040811
00003 50 20040811
00003 99 19870131
Thanks,
Groupby
Mar 17, 2008Hi.I've wrote this query:
SELECT TOP 10 COUNT(*) AS ItemsSold, ProductName, order_details_product_id, SUM(order_details_price) AS order_details_price
FROM tbl_Detail_Order_Product
WHERE (OrderStatus NOT IN (14, 15))
GROUP BY order_details_product_id, ProductName, order_details_price
ORDER BY ItemsSold DESC
which works fine and have these results:
Purchased Returned Sold Part Number
$ 598.29 37 16684
$ 200.29 33 16654
$ 300.29 25 16184
.
.
.
my problem is I want to show the returned column wich is:
WHERE (OrderStatus IN (14, 15))
I dont know how can I do that.
I tried to write another query but it didnt worked correctly.
plz help me.
thanks
How Many Unions?
Jul 18, 2000I know the answer must be somewhere, but for the moment every place I look ...
How many tables can be unioned together (I think I remember that 6.5 had a limit of 16) in SQL7?
Thanks,
Judith
Help Debug Groupby
Jun 15, 2008alter PROCEDURE [dbo].[spsite]
@fromdate datetime,
@todate datetime
AS
BEGIN
select site as sitecode,
count(id) as t from an where mydate >=@fromdate and mydate<=@todate,
count(id) as p from an where p=1 and mydatestage1 >=@fromdate and mydatestage1<=@todate
group by site
what am i doing wrong?
I get incorrect syntax near ,
Question About Sum, GroupBy, Having..
Aug 9, 2006Fred writes "How to retrieve rows for which sum of the qty field equals to a specified number - let's consider a basic table as with 3 fields : item (pk), qty, orderid (fk) :
item qty orderid
**** *** *******
itemA 1 ord-18/07/06
itemB 2 ord-01/01/05
itemC 2 ord-18/07/06
itemD 3 ord-18/07/06
itemE 1 ord-18/07/06
I want to know belong ord-18/07/06's items, which ones to pick up in order to reach a total qty of 5. Answer is itemC (2) + itemD (3). I tried with various combinations of SUM,MAX,HAVING.. Can't make it works. Help appreciated. Rgds."
Sum Of Groupby Query
Feb 19, 2008Hi,
I have a problem regarding this i have this statement where in i can count the number of data and display what category is that.... my problem is how can i sum itHere's the Statement
SELECT Count([PullOut].[SOType]) as Count, [SODescription].[SODescription]
FROM PullOut INNER JOIN SODescription ON [PullOut].[SOType]=[SODescription].[SOType]
WHERE PullOut.Area in ('Rizal','Cavite')
GROUP BY [SODescription].[SODescription];
Output:
1 Category1
5 Category2
2 Category3
....i want only to display the sum of this....
8 Total
Regards
How To Display Records Only Once Using Groupby In
Mar 31, 2008in my sql query,when it run then it displays records as follows
dt_id daily_type dates emp_mgmt_id emp_name etc--------
48 Electrical 03/14/2008 22 abc
----------
49 Mechanical 03/14/2008 35 xyz
-------------
48 Electrical 03/14/2008 22 abc
----------
49 Mechanical 03/14/2008 35 xyz
-------------
i want to display records only once like as follows
dt_id daily_type dates emp_mgmt_id emp_name etc--------
48 Electrical 03/14/2008 22 abc
----------
49 Mechanical 03/14/2008 35 xyz
-------------
if i use group by on some fields then it gives error as follows
Column 'Daily_Time_Entry.daily_type' is invalid in the select list because it is
not contained in either an aggregate function or the GROUP BY clause.
so how can i design query so records display only once not repeated,or is any
alternative,to solve this .
my sql query as follows
SELECT dl.dt_id, dl.daily_type, convert(char,dl.dt_date,101) as
dates,dl.emp_mgmt_id,
emp.emp_name,emp.employee_id,dl.project_type,dl.project_id,
dl.cec_job_id,
cec.cecjobname,dl.time_st,dl.time_ot,dl.time_dt,op.other_proj_id,
op.customer_name,op.project_name,op.owner_rep_phone_num1,dl.work_desc,
m.material_id,m.material_type,m.material_date, m.project_type
,m.project_id,
m.qty,m.description,m.material_unit_price,m.material_markup,
m.material_subtotal,
m.cec_job_id,m.location
FROM material m left outer join
Other_Project op on m.project_id=op.other_proj_id left outer join
Daily_Time_Entry dl on
dl.project_id =op.other_proj_id inner join Employee_Mgmt emp
on emp.emp_mgmt_id = dl.emp_mgmt_id inner join CEC_Job cec
ON dl.cec_job_id = cec.cec_job_id
where (dl.dt_date='3/14/2008'and m.material_date='3/14/2008') and
(dl.project_type='Other Project' and m.project_type = 'Other Project')
and (dl.cec_job_id=m.cec_job_id) and (dl.daily_type='Electrical') and
(dl.project_id='18')
uday
Creating Unions In TSQL
Aug 11, 2004Hi everyone. I was wondering if I could get some pointers in creating a union between two tables. This is the sproc I currently have:
CREATE Procedure spGetReturnCheckForCriteria
@SearchCriteria VARCHAR(8000),
@SortOrder VARCHAR(8000),
@PageSize INT
AS
-- Declare vars
DECLARE @SQLStatement NVARCHAR(4000)
DECLARE @bldSQLStatement VARCHAR(8000)
DECLARE @retValue INT
-- Initialize vars
SET @SQLStatement = ''
SET @bldSQLStatement = ''
SET @retValue = -1
-- Sanity Check(s)
IF (@PageSize IS NULL OR @PageSize < 1)
-- Paging Size can not be Null, nor less than One
BEGIN
SET @RetValue = -30 -- "Must have a valid Paging Size for pagination: Error (-30)
RETURN
END
-- Build the Paging SQL Statement
SET @bldSQLStatement = 'SELECT TOP '
-- Add the Page Size
SET @bldSQLStatement = @bldSQLStatement + CAST(@PageSize AS VARCHAR)
-- Add Columns/Tables/Relationships
SET @bldSQLStatement = @bldSQLStatement + '
ReturnCheck.ReturnCheckID AS ReturnCheckID,
ReturnCheck.FiscalNumber AS FiscalNumber,
ReturnCheck.ReturnedDate AS ReturnedDate,
ReturnCheck.CheckNumber AS CheckNumber,
ReturnCheck.AssessPenaltyIndicator AS AssessPenaltyIndicator,
ReturnCheck.ReturnCheckCollectionStatusCode AS ReturnCheckCollectionStatusCode,
ReturnCheck.ReturnCheckReasonCode AS ReturnCheckReasonCode,
ReturnCheck.CentralCollectionsID AS CentralCollectionsReferralNumber,
TaxPayment.PaymentID AS PaymentID,
TaxPayment.DocumentLocatorNumber AS DocumentLocatorNumber,
TaxPayment.PaymentEffectiveDate AS PaymentEffectiveDate,
TaxPayment.PaymentAmount AS PaymentAmount,
TaxPayment.PaymentQuarter AS PaymentQuarter,
TaxPayment.PaymentYear AS PaymentYear,
TaxPayment.InternalReferenceNumber AS InternalReferenceNumber,
TaxPayment.PaymentTypeCode AS PaymentTypeCode,
TaxPayment.PaymentOriginCode AS PaymentOriginCode,
TaxPayment.VoucherNumber AS VoucherNumber,
TaxPayment.ReversedIndicator AS ReversedIndicator,
TaxPayment.PaymentDate AS PaymentDate,
CAST(NULL AS DATETIME) AS CCReferralDate,
DistributionPoint.UIDPrime AS UIDPrime,
DistributionPoint.UIDCheck AS UIDCheck,
DistributionPoint.UIDDistPoint AS UIDDistPoint,
CASE
WHEN ReturnCheck.UpdatedDate IS NULL THEN ReturnCheck.CreatedDate
ELSE ReturnCheck.UpdatedDate
END AS ReturnCheckTimeStamp
FROM TaxPayment
INNER JOIN DistributionPoint
ON (TaxPayment.DistributionPointID = DistributionPoint.DistributionPointID)
INNER JOIN ReturnCheck
ON (TaxPayment.PaymentID = ReturnCheck.PaymentID)
'
-- Add Search Criteria
If (@SearchCriteria IS NOT NULL)
SET @bldSQLStatement = @bldSQLStatement + ' WHERE ' + @SearchCriteria
-- Add Sort Order
IF (@SortOrder IS NOT NULL)
SET @bldSQLStatement = @bldSQLStatement + ' ' + @SortOrder
-- Set the SQLStatement
SET @SQLStatement = @bldSQLStatement
-- Execute the Paging Query
EXECUTE @retValue = sp_executeSQL @SQLStatement
GO
Look at the SQL build where I'm doing an INNER JOIN between TaxPayment and RefundCheck. Instead of this INNER JOIN, I'd like to do a union instead. If I can get any help on this I'd greatly appreciate it. Cheers.
Syntax Error With Unions?
Oct 2, 2001I doing a union of two select queries, and I keep getting
the following error:
syntax error converting the nvarchar value 'foo' to a column of data type int.
I've tried using CAST and CONVERT in the select statement, but it doesn't change the outcome.
The table it's complaining about (the one containing the value 'foo'), is of data type nVarChar, so I don't have any idea why SQL server would try to convert it to an integer.
Is this a common problem? I'd love to know what I'm doing wrong.
Syntax Error With Unions?
Oct 2, 2001I doing a union of two select queries, and I keep getting
the following error:
syntax error converting the nvarchar value 'foo' to a column of data type int.
I've tried using CAST and CONVERT in the select statement, but it doesn't change the outcome.
The table it's complaining about (the one containing the value 'foo'), is of data type nVarChar, so I don't have any idea why SQL server would try to convert it to an integer.
Is this a common problem? I'd love to know what I'm doing wrong.
Ordering In Conjunction With Unions
Oct 7, 2004Why does this not work? When I run this query in Query Analyzer, I get the error "Incorrect syntax near the keyword 'UNION'." This seems simple enough...
SELECT * FROM SalesLead WHERE Age = '50' ORDER BY FirstName
UNION ALL
SELECT * FROM SalesLead WHERE Age = '60' ORDER BY FirstName
Thanks for any advice
Aaron
Performance Issue With Unions In Sp's?
Jul 20, 2007I was having a chat with a chap over lunch today and he asked if I knew of any performance issues when doing unions in stored procedures. I couldn't think of anything but he seemed sure there was.
Is there such an issue I've missed?
Mike
Subquery Headache With Count And GroupBy
Nov 23, 2005I'm trying to return an integer from the following table that returnsthe number of unique cities:tblEmployeesName CityJohn BostonFrank New YorkJim OmahaBetty New YorkThe answer should be 3.DECLARE @EmployeeCities intSELECT @EmployeeCities = SELECT ... ???How go I return one row/column into @EmployeeCities from a Count and aGroupBy?Headache already... Maybe it's too early...
View 3 Replies View RelatedDoing Unions With Uneven Column Amounts
Mar 14, 2006I'm creating a program that allows users to submit a report on equipment at regular intervals. If a piece of equipment has a problem, it is given a job entry that refers back to the report for various information.
However, there will be times when a problem is noticed, and someone wants to submit it immediately; these are made into extra jobs.
To this end, I have three tables:
Reports
Jobs
ExtraJobs
Because ExtraJobs cannot be associated with a report, they have their own table, which holds information that would otherwise be grabbed from both Reports and Jobs. While there are seperate submission forms for regular jobs and extra jobs, I would like them to appear on the same query result when a user looks at submitted jobs or reports.
What I'm currently trying to do is this:
Code:
SELECT*
FROMReports LEFT JOIN Jobs
ON Reports.reportID = Jobs.reportID
UNION ALL
SELECT ExtraJobs.*
FROM ExtraJobs
This won't work because the first half of the union has an extra column (reportID) that the second half does not. Is there any way to add in a value for that non-existant column (say, ExtraJobs.reportID = -1) to make sure that both sides have an equal number of columns?
If worst comes to worst, I could add a reportID column to ExtraJobs and have it set to -1 for everything, but I'd like to keep from adding fat, if at all possible.
Unions Two Tables Into A Temp Table
Aug 31, 2007How do I do this? I have two queries that create temp tables. I need to union them together and create one temp table. Anyone done this with success?
View 4 Replies View RelatedWay To Group / Count Multiple Unions Together?
Dec 9, 2013I have 4 archive tables and 1 active table that are created the same, but contain different data based on the date. I need to get results that have three columns: AuthorName, Month, Total. This is currently working, but through my research I can't find how to start going about dealing with the fact that each Author has some of his results from one month in one table and some in another table and how to add those together into one row. Example:
(What I'm Getting)
AuthorName Month Total
Test, Fred 3 43
Test, Fred 3 12
Test, Fred 2 56
Test, Fred 5 35
[code]....
View That Unions Two Other Views Does Not Work
May 1, 2006I created a view V1 that uses an outer join with a table and calls a sub-view VS1 (ds_proj_report_date) which uses an inner join) and does an inner join with VS1. I also created another view V2 that uses inner join but does not call the sub-view VS1.
When I run the two views as below it works fine
Select * from V1
Union
Select * from V2
I then created another view V3 of the above union as
Create view V3
As
Select * from V1
Union
Select * from V2
Now when I run select * from V3, I get the following error.
Joined tables cannot be specified in a query containing outer join operators. View or function 'ds_proj_report_date' contains joined tables
Delete Records Not Matching Top25 In GroupBy
Jul 23, 2005I have a table (tblA) that records the RecordID, UserID andLastViewedDate (DateTime) of each record opened in tblB where RecordIDis the PK in tblB. I want to construct a query that groups all recordsin tblA by RecordID, filters by UserID and keeps only the most recent25 RecordIDs and deletes the rest.This gets me a recordset of all RecordIDs filtered by UserID in tblAbut I can't figure out how to sort it by LastViewedDate DESC and toeliminate those not in the Top25:SELECT RecordIDFROM dbo.tblAWHERE (UserID = 1234)GROUP BY RecordIDAny help is appreciated!lq
View 4 Replies View RelatedReverse GroupBy Kind Of Flat File...
Nov 16, 2007Hi!
I have a data (txt) file which has the following structure
M_ID1:
CID11,Date11
CID12,Date12
M_ID2:
CID,Date21
CID,Date22
My problem now is it, to get that into a table (MID,CID,DATE) as the text file "groups" CID and DATE by MID and
I have no idea how to read the file and transform it.
Well, perhaps somebody out there has an idea!
Thanks in advance!
alx
T-SQL (SS2K8) :: How To Add Manual Column Based On GroupBy For Title
Jan 15, 2015I have a query to get data group by High, Medium and Low so only three rows will be returned.
select
sum(case .... ) then 1 else 0 end) as [Column1],
sum(case .... ) then 1 else 0 end) as [Column2],
sum(case .... ) then 1 else 0 end) as [Column3]
from sometable
Group by High, Medium, Low
I want to manually add High, Medium and Low to each row's first column. the final result should look like:
Column1 Column2 Column3
High 123 123 123
Medium 123 123 123
Low 123 123 123
How can I do it?
SQL Server 2012 :: Using Unions To Write Out Each Select Query As Distinct Line In Output
Aug 20, 2015Basically I'm running a number of selects, using unions to write out each select query as a distinct line in the output. Each line needs to be multiplied by -1 in order to create an offset balance (yes this is balance sheet related stuff) for each line. Each select will have a different piece of criteria.
Although I have it working, I'm thinking there's a much better or cleaner way to do it (I use the word better loosely)
Example:
SELECT 'Asset', 'House', TotalPrice * -1
FROM Accounts
WHERE AvgAmount > 0
UNION
SELECT 'Balance', 'Cover', TotalPrice
FROM Accounts
WHERE AvgAmount > 0
What gets messy here is having to write a similar set of queries where the amount is < 0 or = 0
I'm thinking something along the lines of building a table function contains all the descriptive text returning the relative values based on the AvgAmount I pass to it.
Join First Query With A Query That Contains Unions
Jun 10, 2013I have to queries I need to combine with a left join and I am having trouble figuring out the syntax. I need to join the first query with a query that contains Unions. The queries need to by joined on File_NBR which is contained in vw_SBC_Employee_Info, vw_ADPFile and SBC_Best_Scores.
Query 1
SELECT
e1.File_NBR,
e1.Division,
e1.Department,
e1.Program,
e1.UNIT,
(e6.Mngr_FName + ' ' + e6.Mngr_LName) AS President,
[Code] .....
Query 2
SELECT
'Skill Rating' as Assessment_Type,
bs.File_NBR as ID,
bs.Skill_NBR,
bs.Best_Score as Score,
bs.Assesment_RND
[Code] .....