SQL Server 2008 :: Using Expression In GROUP BY Clause
Feb 23, 2015
I am working to move an application from MySQL to SQL Server. The person who developed the MySQL application has little database experience, and took some shortcuts that the lax nature of MySQL allows. One query with which I am struggling looks something like this:
SELECT StartTime + Offset, min(Sensor), max(Sensor)
FROM SensorData
WHERE SensorID = @SensorID AND
StartTime + Offset > @BeginTime AND
StartTime + Offset < @EndTime
GROUP BY (StartTime + Offset) / 100
ORDER BY StartTime + Offset
What we are trying to accomplish is to return minimum and maximum sensor values over a number of periods between the BeginTime and EndTime. When I run this query in MySQL on a sample dataset, it returns a small number of rows, with each one being the min/max values for a portion of the overall period.
Under MS SQL Server 2008, SP3, I get the two following error messages:
Column 'SensorData.StartTime' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Column 'SensorData.Offset' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
View 9 Replies
ADVERTISEMENT
Mar 5, 2015
SELECT DISTINCT
'Banquets - All Day' as revName,
SUM(t.c_items_total) AS Banquet_Total,
SUM(t.cover_count) as Total_Covers,
-- (t.c_items_total) / (t.cover_count) as AvgPer_Cover--
[Code] ....
The output needs to be grouped by the t.c_items_total...I just need the avg per cover (person) / items_total.
View 4 Replies
View Related
Apr 13, 2015
While running the below query, getting the error: Am I missing any of the columns to include in the SELECT column_list?
Msg 8120, Level 16, State 1, Line 1
Column 'sys.master_files.database_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
<code>
select a.[Database Name],a.[Type],a.[Size in MB],b.LastUserUpdate
from
(
SELECT database_id,[Database Name]= DB_NAME(database_id),
[Type]= CASE WHEN Type_Desc = 'ROWS' THEN 'Data File(s)'
WHEN Type_Desc = 'LOG' THEN 'Log File(s)'
ELSE Type_Desc END ,
[code]...
View 3 Replies
View Related
Mar 8, 2007
I have an expression in a group that calculates a percentage of sales:
=iif(Sum(Fields!BOOKD.Value)=0,0,IIF(Sum(Fields!NET.Value)=0,0,((Sum(Fields!BOOKD.Value)-Sum(Fields!NET.Value))/Sum(Fields!BOOKD.Value))))*100
If there is a 0 balance for both the book dollar and the net dollar the expression returns #Error.
I have made sure that if the value is null it should return a 0 in the initial query.
,SUM(ISNULL(R.BOOKD,0)) BOOKD
,SUM(ISNULL(R.NET,0)) NET
Any suggestions would be greatly appreciated.
View 1 Replies
View Related
Feb 26, 2015
I am developing the SSIS and stuck on copying specific files.
1. We receive CSV file to our drive on a daily basis.
2. The csv file name has the last 8 digits formatted with the yyyymmdd. For example, the file name might be abcdef_20150226.csv This means it will be our CSV file for today, Thursday, February 26, 2015.
3. There are a lot of files in this directory.
[URL]
What we would like to do:
Add the constraints (or expression) that will copy the files from this directory to another directory that have the date equivalent to Monday only. For example, the file abcdef_20150226.csv will not be copied because it is Thursday file. But the abcdec_20150223 will be copied to a new Directory because it is Monday.
View 1 Replies
View Related
Apr 2, 2015
I am trying to create a small data set for use in a charting program. However, I am not getting the results that I expected.
********QUERY BEGIN*****************
DECLARE @BeginDate datetime
DECLARE @EndDate datetime
SET @BeginDate = '2014-01-01'
SET @EndDate = '2014-12-31';
WITH CTE AS (
SELECT bkg_nbr
[Code] ....
The raw data from the CTE is:
Bkg_nbr STATE_NBR Ranking
20140000943200060000 1
20140000131500140000 1
20140000682200140000 2
20140000504100210000 1
The result of the query above was:
Category RecCount
Non-Recidivists3
Recidivists 1
The outcome I was looking for was:
Category RecCount
Non-Recidivists2
Recidivists 1
What I am trying to do is count persons in buckets "non-recidivists" and "recidevists" based on how many bkg_nbr they have per STATE_NBR. If they have more than 1 bkg_nbr per STATE_NBR then put them in the "recdivists" bucket. If they only have a 1 to 1 then put them in the "non-recidivists" bucket.
View 2 Replies
View Related
Apr 10, 2015
I am getting an error when running this query in SSRS- "an expression of non-boolean type specified in a context where a condition is expected , near ',' " on the following query in the WHERE clause section.
SELECT
MG_BL_ITINERARY.ETA_DT,
MG_BL_ITINERARY.TO_LOCATION_CD
FROM MG_BL_ITINERARY
[code]...
what I need to change in the WHERE clause to rectify this error ?
View 3 Replies
View Related
May 14, 2008
2 examples:
1) Rows ordered using textual id rather than numeric id
Code Snippet
select
cast(v.id as nvarchar(2)) id
from
(
select 1 id
union select 2 id
union select 11 id
) v
order by
v.id
Result set is ordered as: 1, 11, 2
I expect: 1,2,11
if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine.
2) SQL server reject query below with next message
Server: Msg 169, Level 15, State 3, Line 16
A column has been specified more than once in the order by list. Columns in the order by list must be unique.
Code Snippet
select
cast(v.id as nvarchar(2)) id
from
(
select 1 id
union select 2 id
union select 11 id
) v
cross join (
select 1 id
union select 2 id
union select 11 id
) u
order by
v.id
,u.id
Again, if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine.
It reproducible on
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
and
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
In both cases database collation is SQL_Latin1_General_CP1251_CS_AS
If I check quieries above on database with SQL_Latin1_General_CP1_CI_AS collation then it works fine again.
Could someone clarify - is it bug or expected behaviour?
View 12 Replies
View Related
Apr 14, 2015
I have a query needs to look for 5 records data in a table. Basically i need to hardcode. Below is my query which didn't work out.
select BF_ORGN_CD, BF_BDOB_CD, BF_TM_PERD_CD,data
from BF_DATA
WHERE (BF_ORGN_CD,BF_BDOB_CD,BF_TM_PERD_CD) in ***** i guess this is the wrong query****
('A1', 'B1', 'C1')
('A2', 'B2', 'C2')
('A3', 'B3', 'C3')
('A4', 'B4', 'C4')
('A5', 'B5', 'C5')
but if i use the query below it will generate more records than these 5 records
select BF_ORGN_CD, BF_BDOB_CD, BF_TM_PERD_CD,data
from BF_DATA
WHERE (BF_ORGN_CD) in ('A1', 'A2', 'A3', 'A4', 'A5')
and (BF_BDOB_CD) in ('B1', 'B2', 'B3', 'B4', 'B5')
and (BF_TM_PERD_CD) in ('C1', 'C2', 'C3', 'C4', 'C5')
View 4 Replies
View Related
Jul 16, 2015
We're encrypting a column of Social Security numbers. Developer needs to execute a query -->similar<-- to:
Select <bunch of columns>
From dbo.People p
Where p.SSN = @Input_SSN_Param --I know this won't work.
If we write the where clause like
Where DecryptByKey(p.SSN) = @Input_SSN_Param
Won't every SSN in the table be decrypted? Seems like a bad idea, performance-wise.
Should I/we add a column to hold a hash of the SSN; and use the hash for comparisons?
View 3 Replies
View Related
Feb 6, 2008
HiI am new to SQL and am having a problem. I need to fix my query to do the following...2) get a total of the number of rows returned.
DECLARE @StartDate varchar(12)DECLARE @EndDate varchar(12)DECLARE @Region varchar(20)
SET @StartDate = '01/01/2002'SET @EndDate = '12/31/2008'SET @Region = 'Central'
SELECTA.createdon,A.casetypecodename,A.subjectidname,A.title,A.accountid,A.customerid,A.customeridname,B.new_Region,B.new_RegionName
FROM dbo.FilteredIncident AINNER JOIN dbo.FilteredAccount B ON A.customerid = B.accountid
WHERE (A.createdon >=@StartDate AND A.createdon <= @EndDate)AND (B.new_RegionName = @Region)AND (A.casetypecode = 2)
View 1 Replies
View Related
Feb 19, 2014
I'm trying to write a query to select various columns from 3 tables. In the where clause I use a set of conditions, but most important condition is that I only want to see all results from the different columns where the ph.ProdHeaderDossierCode contains at least 25 lines of processed hours. I tried this with group by and having, but I constant get error messages on all other columns that I want to see: "is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause". How can I make this so I can see all information I need?
Here is my code so far:
selectph.CalculatedTotalTime,
ph.ProdHeaderDossierCode,
ph.MachGrpCode,
ph.EmpId,
pd.PartCode
fromdbo.T_ProcessedHour ph,
[Code] ....
View 8 Replies
View Related
Aug 3, 2015
I'm working on a query in which I need to get few nodes values from the XML data by using the value from SQL column (MessageContentType) in this query. I'm able to get the nodes value when i hard code the value in the query but the problem is MessageContentType will vary from some records in the table, due to that I'm not getting the corresponding node values. I have tried few ways to get this value dynamically but I'm missing something.
Sample Table Data
MessageContentType | BodySegment
xx:ADT_A03_26_GLO_DEF | <ns0:ADT_A03_26_GLO_DEF xmlns:ns0="http://microsoft.com/HealthCare/HL7/2X">.....
Current Query - HardCode Script
SELECT
ID,MsgContentType
BODYSEGMENT,
BODYSEGMENT.value('declare namespace xx="http://microsoft.com/HealthCare/HL7/2X"; /xx:ADT_A03_26_GLO_DEF[1]/colxx[1]/colxx[1]','varchar(300)') AS TimeSpan
FROM
s
When i tried the below line of script, I'm getting this error "[color=#FF0000]The argument 1 of the XMLdata type method "value" must be a string literal.[/color]"
Concat MsgContentType Column
BODYSEGMENT.value('declare namespace xx="http://microsoft.com/HealthCare/HL7/2X"; /'+MsgContentType+'[1]/EVN_EventType[1]/EVN_2_RecordedDateTime[1]','varchar(300)') AS TimeSpan
To overcome that error i used sql column but I'm getting this error [color=#FF0000]XQuery [S.bodysegment.value()]: Syntax error near '[', expected a "node test"[/color].
BODYSEGMENT.value('declare namespace xx="http://microsoft.com/HealthCare/HL7/2X"; /[sql:column("MsgContentType")][1]/EVN_EventType[1]/EVN_2_RecordedDateTime[1]','varchar(300)') AS TimeSpan
I tried this line of script, i didn't get any error by timespan is coming as null, I do hope this script not pointing the correct node to traverse the sibling node.
BODYSEGMENT.value('declare namespace xx="http://microsoft.com/HealthCare/HL7/2X"; /*[local-name()=sql:column("MsgContentType")][1]/EVN_EventType[1]/EVN_2_RecordedDateTime[1]','varchar(300)') AS TimeSpan
View 9 Replies
View Related
Jul 23, 2005
Hi, can anyone shed some light on this issue?SELECT Status from lupStatuswith a normal query it returns the correct recordcountSELECT Status from lupStatus GROUP BY Statusbut with a GROUP By clause or DISTINCT clause it return the recordcount= -1
View 3 Replies
View Related
Oct 31, 2015
Despite the products are different, I want to get the sum of all products according to customer card.(client ref)
select (ORF.PRICE) *( ORF.AMOUNT - ORF.SHIPPEDAMOUNT) from [.dbo.LG_032_01_ORFLINE ORF JOIN [.dbo.LG_32_01_ITEMS ITM ON ORF.STOCKREF = ITM.LOGICALREF where ORF.CLIENTREF = XXX
View 1 Replies
View Related
Jan 29, 2015
I have a table returning results like that
Row1 ||Row2 ||ERPID||ParentID||LevelID||Category||SubCategory||DDate ||publish
1 ||1 ||10152159||1015 ||2159 ||LOCTITE ||LOCTITE1||29/01/2015 12:10||0
1 ||2 ||10152134||1015 ||2134 ||LOCTITE ||LOCTITE2||29/01/2015 12:10||0
1 ||3 ||10152157||1015 ||2157 ||LOCTITE ||LOCTITE3||29/01/2015 12:10||0
2 ||1 ||10062003||1006 ||2003 ||COMPUTER||COMPUTER1||29/01/2015 12:10||1
[code]....
But I want to look like that
Row1||Row2||ERPID||ParentID||LevelID||Category||SubCategory||DDate||publish
1||1||10151015||1015 ||1015||LOCTITE||||29/01/2015 12:10||0
1||1||10152159||1015 ||2159||LOCTITE||LOCTITE1||29/01/2015 12:10||0
1||2||10152134||1015||2134||LOCTITE||LOCTITE2||29/01/2015 12:10||0
1||3||10152157||1015||2157||LOCTITE||LOCTITE3||29/01/2015 12:10||0
2||2||10061006||1006||1006||COMPUTER||||29/01/2015 12:10||1
[code]....
View 3 Replies
View Related
Feb 18, 2015
I have a set of rows in a table like for example
Client ID Client Name Date Score
1 Smith 12/31/2014 25
1 Smith 10/15/2014 45
2 John 08/11/2014 55
2 John 06/18/2014 15
3 Rose 04/15/2014 12
4 Mike 07/23/2014 28
5 Mary 01/5/2014 56
6 Lisa 08/1/2014 54
6 Lisa 05/10/2014 34
Now I want to use Row Number function or any way where I can get the result as below
Client ID Client Name Date Score RowNo
1 Smith 12/31/2014 25 1
1 Smith 10/15/2014 45 2
2 John 08/11/2014 55 1
2 John 06/18/2014 15 2
3 Rose 04/15/2014 12 1
4 Mike 07/23/2014 28 1
5 Mary 01/5/2014 56 1
6 Lisa 08/1/2014 54 1
6 Lisa 05/10/2014 34 2
View 3 Replies
View Related
Aug 5, 2015
I have this data below :
isin | market | ccy| stock_mkt | max(a.year) | max(a.month ) | max(a. day)
DEX | XFFA | DEM | 013 | 2009 | 5 | 8
DEX | XFFA | EUR | 013 | 2014 | 11 | 25
get this data by running the following query :
select b.isin, a.market, a.ccy, a.stock_mkt, max(a.year)
,max(a.month), max(a.day) from market_table a inner join
isin_table b on a.id = b.id where a.isin_closing_date=0
and a.market in ('XFFA', 'XFFA') and
b.isin_type= 'I' and b.isin = 'DEX' group by b.isin,
a.market, a.ccy, a.stock_mkt
what's needed is to get the earliest record all times, no mater the currency :
isin | market | ccy| stock_mkt | max(a.year) | max(a.month ) | max(a. day)
DEX | XFFA | EUR | 013 | 2014 | 11 | 25
View 3 Replies
View Related
Mar 16, 2015
I'm presented with an issue where by I need to reclaim a fair bit of unused space currently sat in the primary data file for my database. I don't want to run DBCC SHRINKDATABASE as we all know this could potentially have a some serious negative effects relating to index fragmentation.
So, how do I get the free space out of the data file? - I've decided to:
1. Add new new file group
2. Add a clustered index for all tables on the new file group
3. Shrink the primary file group as much as possible (hopefully giving me the free space back)
4. Drop the newly created clustered indexes for all tables
There are no clustered indexes currently for any of the tables!, so me temporarily creating/dropping one shouldn't be an issue. Are there any other ways I can get the free space back to the OS?
View 8 Replies
View Related
May 25, 2015
I have groups of records in a table, and I would like to set a necessary condition on each group. The condition is that EXACTLY ONE of the records in each group has a flag field set to True (bit = 1). I can naturally write triggers for update, insert and delete events that test for such a condition.
Something along the lines of this condition:
(select count(ClovekAutoID)
from TableOfClovekNames tCN
where JeHlavni = 1
group by ClovekAutoID
having COUNT(JeHlavni ) > 1) = 1In fact,
I tried this just on whim, but naturally, the SS engine told me to go roll my hoop, that subqueries are not allowed in constraint expressions.
View 9 Replies
View Related
Aug 25, 2015
I have a scenario as below for one ID -
+------+--------+----------------------------+-------+
| id | amount | date | descr|
+------+--------+-----------------------------+------+
| 5689 | 10.00 | 2015-08-25 12:10:57.107 | 4 |
| 5689 | 10.00 | 2015-08-24 12:07:57.107 | 3 |
| 5689 | 10.00 | 2015-08-25 12:05:57.107 | 3 |
| 5689 | 130.00 | 2015-08-24 12:07:57.107 | 4 |
| 5689 | 130.00 | 2015-08-25 12:07:57.107 | 3 |
+------+--------+-----------------------------+-----+
I want to fetch below 3 records from the above scenario i.e. latest record of each amount (Latest is determined using "descr" column i.e. 4 is greater then 3 -
+------+--------+----------------------------+-------+
| id | amount | date | descr|
+------+--------+-----------------------------+------+
| 5689 | 10.00 | 2015-08-25 12:10:57.107 | 4 |
| 5689 | 10.00 | 2015-08-24 12:07:57.107 | 3 |
| 5689 | 130.00 | 2015-08-24 12:07:57.107 | 4 |
+------+--------+-----------------------------+-----+
But in case of same amounts I am unable to fetch the latest status as even using partitioning will treat them as one.
CREATE TABLE #TMP
(
ID INT,
AMOUNT DECIMAL,
[DATE] DATETIME,
DESCR VARCHAR(10)
)
INSERT INTO #TMP VALUES
(5689,10.00,'2015-08-25 12:10:57.107','4')
,(5689,10.00,'2015-08-24 12:07:57.107','3')
,(5689,10.00,'2015-08-25 12:05:57.107','3')
,(5689,130.00,'2015-08-24 12:07:57.107','4')
,(5689,130.00,'2015-08-25 12:07:57.107','3')
View 8 Replies
View Related
Sep 10, 2015
Copy and paste the code to review sample:
Create table #temp(
ID varchar (20),
ID_BegAttach varchar (20),
ID_EndAttach varchar (20),
ID_AttachLvl varchar (20),
ID_NumofAttach varchar (20)
[Code] ...
I'd like to get the column ID_NumofAttach to be populated by the total number of ID_Attachlevel column by the same ID_BegAttach or ID_EndAttach and populate where ID_Attachlevel is 0.
View 9 Replies
View Related
Nov 6, 2015
I have a scenario where the End User is going to select two parameters. @AgentID and @Location If the End User selects @AgentID parameter, in my WHERE clause, I do not want to check the location condition. By default, the agent knows their location already.
If the End User select @Location parameter, in my WHERE clause, I still want to check the Agent condition.
@AgentID parameter would populate either with "All" or their actual agent# (76754 for example).
@Location parameter would populate either with "All" or their actual location (Birmingham or Bessemer for example)Sample of my current WHERE clause:
WHERE
gl.ReceiptDate >= @BeginDate and gl.ReceiptDate < DATEADD(D,1,@EndDate)
AND gl.AgentId = CASE WHEN @AgentID = 'All' THEN gl.AgentID ELSE @AgentID END
AND gl.Location = @Location
View 9 Replies
View Related
Sep 1, 2015
We are receiving following alerts frequently about 1:40 AM in the morning. We have backups running on 11:00 PM everyday and rebuild job running at 2:00 AM. Not sure the exact cause of this error.
Error:
The file group "PRIMARY" for the database "tempdb" in SQL instance "MSSQLSERVER" on computer "XYZ" is running out of space.
tempdev Initial size : 133,100 MB Growth: By 10 percent, Limited to 140000 MB
templog Initial Size : 5,475 MB Growth: By 10 percent, Unlimited
View 9 Replies
View Related
May 13, 2015
is there any more efficient way for example to implement the next query?
SELECT s1.article, dealer, s1.price
FROM shop s1
JOIN (
SELECT article, MAX(price) AS price
FROM shop
GROUP BY article) AS s2
ON s1.article = s2.article AND s1.price = s2.price;
WHERE dealer = 'dealer sample'
What indexes I should create for this query?
View 2 Replies
View Related
Oct 16, 2013
I have a query however i am getting the following error message “Msg 447, Level 16, State 0, Line 1 Expression type numeric is invalid for COLLATE clause.“
This is my query
SELECT
sjo.ID,
sjo.MID,
sjo.Trade_Association_Name,
da1.Account_Name As Trade_Association_Name,
substring(do.[MM-CHN-AGENT],2,12) as Mass_Agent_Chain_No,
[Code] ....
And Dan.Stg_Jitter_Opp2 table consists of the following
ColumnNameData Type
Idvarchar(50)
Mid numeric(18, 0)
RecordTypeIDvarchar(50)
Trade_Association_Name varchar(50)
And [FDMS].[SalesForce].[DailyAccounts]table consists of the following
ColumnNameData Type
Idint
Account_Idvarchar(18)
account_Name varchar(150)
mid_externalvarchar(15)
Mid_internalvarchar(15)
View 5 Replies
View Related
Dec 10, 2007
HI,
I tried to run the following query.
Code Block
WITH YesterdayCTE AS
(
SELECT type = 'Members Joined Yesterday'
, Borrowers = (select count(*)
from LoanApplication INNER JOIN
Member ON LoanApplication.MemberFK = Member.Id AND LoanApplication.Id = Member.LastLoanApplicationFK INNER JOIN
CreditUnion ON Member.CreditUnionFK = CreditUnion.Id
where (LoanApplication.SubmittedOn >= GETDATE()-1) AND (Member.CuStatus = 'Approved')
GROUP BY CreditUnion.Name )
, Depositors = (select count(*)
from CDOrder INNER JOIN
Member ON CDOrder.MemberFK = Member.Id AND CDOrder.Id = Member.LastCDOrderFK INNER JOIN
CreditUnion ON Member.CreditUnionFK = CreditUnion.Id
where (CDOrder.SubmittedOn >= GETDATE()-1) AND (Member.CuStatus = 'Approved')
GROUP BY CreditUnion.Name )
),
MonthlyCTE AS
(
SELECT type = 'Members Joined Last Month'
, Borrowers = (select count(*)
from LoanApplication INNER JOIN
Member ON LoanApplication.MemberFK = Member.Id AND LoanApplication.Id = Member.LastLoanApplicationFK INNER JOIN
CreditUnion ON Member.CreditUnionFK = CreditUnion.Id
where (LoanApplication.SubmittedOn >= GETDATE()-30) AND (Member.CuStatus = 'Approved')
GROUP BY CreditUnion.Name )
, Depositors = (select count(*)
from CDOrder INNER JOIN
Member ON CDOrder.MemberFK = Member.Id AND CDOrder.Id = Member.LastCDOrderFK INNER JOIN
CreditUnion ON Member.CreditUnionFK = CreditUnion.Id
where (CDOrder.SubmittedOn >= GETDATE()-30) AND (Member.CuStatus = 'Approved')
GROUP BY CreditUnion.Name )
),
YearlyCTE AS
(
SELECT type = 'Members Joined Last year'
, Borrowers = (select count(*)
from LoanApplication INNER JOIN
Member ON LoanApplication.MemberFK = Member.Id AND LoanApplication.Id = Member.LastLoanApplicationFK INNER JOIN
CreditUnion ON Member.CreditUnionFK = CreditUnion.Id
where (LoanApplication.SubmittedOn >= GETDATE()-360) AND (Member.CuStatus = 'Approved')
GROUP BY CreditUnion.Name )
, Depositors = (select count(*)
from CDOrder INNER JOIN
Member ON CDOrder.MemberFK = Member.Id AND CDOrder.Id = Member.LastCDOrderFK INNER JOIN
CreditUnion ON Member.CreditUnionFK = CreditUnion.Id
where (CDOrder.SubmittedOn >= GETDATE()-360) AND (Member.CuStatus = 'Approved')
GROUP BY CreditUnion.Name )
),
combinedCTE AS
(
SELECT * FROM YesterdayCTE
UNION ALL
SELECT * FROM MonthlyCTE
UNION ALL
SELECT * FROM YearlyCTE
)
SELECT *
, Members = Borrowers + Depositors
FROM combinedCTE
But I get the following error message.
An error occurred while reading data from the query result set.
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. (Microsoft Report Designer)
===================================
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. (.Net SqlClient Data Provider)
Please can anyone help me to make this work?
Thanks
View 7 Replies
View Related
Aug 11, 2005
Please advise on how to get the GROUP BY coded in an acceptable way:DECLARE @LO INTDECLARE @HI INTDECLARE @StartDate varchar(10)DECLARE @EndDate varchar(10)SELECT @StartDate = '01/01/2005'SELECT @EndDate = '06/30/2005'SELECT @LO= 250SELECT @HI= 333SELECTStateCD, CountyCD, Zip, Z.CityName, Z.StateCode, Z.CountyName, 'Criteria' = 'JumboRange:' + Convert(varchar(4),@LO) + '-' +Convert(varchar(4),@HI), 'StartingDate' = @StartDate, 'ThruDate' = @EndDate, JumboAmount = SUM(JumboAmount), JumboMortgages = SUM(JumboMortgages), JumboFIXMortgages= SUM(JumboFIXMortgages), JumboFIXAmount = SUM(JumboFIXAmount), JumboARMMortgages = SUM(JumboARMMortgages), JumboARMAmount= SUM(JumboARMAmount)FROM LoanDetails T INNER JOIN dbo.ZipCodesPreferred ZON T.StateCD = Z.FIPS_State AND T.CountyCD = Z.FIPS_County AND T.Zip =Z.ZipCodeGROUP BYStateCD, CountyCD, Zip, Z.CityName, Z.StateCode, Z.CountyName, 'Criteria' = 'JumboRange:' + Convert(varchar(4),@LO) + '-' +Convert(varchar(4),@HI), 'StartingDate' = @StartDate, 'ThruDate' = @EndDate
View 2 Replies
View Related
Mar 23, 2008
I have an exisitng report that lists unit name, provider, runday, shift, patient. The report groups by unit name and there is a page break after each unit name. So in the current environment the report prints one page per unit that contains all of the providers, rundays, shifts, and patients for that unit name its grouping on. So the report would like like this when it prints:
Unit A
Provider 1 Runday 1 Shift 1
patient 1
patient 2
Provider 1 Runday 1 Shift 2
patient 1
patient 2
Provider 1 Runday 2 Shift 1
patient 1
patient 2
Provider 1 Runday 2 Shift 2
patient 1
patient 2
Provider 2 Runday 1 Shift 1
patient 1
patient 2
Provider 2 Runday 1 Shift 2
patient 1
patient 2
Provider 2 Runday 2 Shift 1
patient 1
patient 2
Provider 2 Runday 2 Shift 2
patient 1
patient 2
PAGE BREAK
Unit B............(repeat data from page 1)
PAGE BREAK
Unit C............(repeat data from page 1
The end user would like the ability to keep the report as is but would like to also be able to print the report as one page per each unit name, provider, runday and shift. so it would look like this
Unit A
Provider 1 Runday 1 Shift 1
patient 1
patient 2
PAGE BREAK
Unit A
Provider 1 Runday 1 Shift 2
patient 1
patient 2
PAGE BREAK
Unit A
Provider 1 Runday 2 Shift 1
patient 1
patient 2
So I created a boolean parameter with a prompt of Page break by Unit, Provider, Runday & Shift? My thought is if the users sets this to False, the report will group on just Unit Name (the first example). If the user sets this to True, the report will group on Unit Name, Provider, Runday & Shift.
I set the grouping expression for this data table as:
=iif(Parameters!Grouping.Value = "False", Fields!unit_Name.Value,(Fields!unit_Name.Value,Fields!Lname.Value,Fields!Rundays.ValueFields!Shift.Value))
Within the expression editor window it displays a syntax error and the report will not run.
Any help would be greatly appreciated!!!!!!!!!
View 7 Replies
View Related
Mar 8, 2007
I have an expression in a group that calculates percent of sales:
=iif(Sum(Fields!BOOKD.Value)=0,0,IIF(Sum(Fields!NET.Value)=0,0,((Sum(Fields!BOOKD.Value)-Sum(Fields!NET.Value))/Sum(Fields!BOOKD.Value))))*100
The problem I'm having is that if both the
Sum(Fields!BOOKD.Value)=0 and
Sum(Fields!NET.Value)=0 ,
the expression returns the message #ERROR instead of a 0.
I have the initial query set to display 0 if the value is null.
,SUM(ISNULL(R.BOOKD,0))BOOKD
,SUM(ISNULL(R.NET,0))NET
I would greatly appreciate any help on how to solve this issue.
Thank Barb
View 4 Replies
View Related
Nov 20, 2004
Hi,
What is HAVING clause equivalent in the following oracle query, without the combination of "GROUP BY" clause ?
eg :
SELECT SUM(col1) from test HAVING col2 < 5
SELECT SUM(col1) from test WHERE x=y AND HAVING col2 < 5
I want the equivalent query in MSSQLServer for the above Oracle query.
Also, does the aggregate function in Select column(here the SUM(col1)) affect in anyway the presence of HAVING clause?.
Thanks,
Gopi.
View 3 Replies
View Related
Apr 3, 2008
How Can I use Top Clause with GROUP BY clause?
Here is my simple problem.
I have two tables
Categories
Products
I want to know Top 5 Products in CategoryID 1,2,3,4,5
Resultset should contain 25 Rows ( 5 top products from each category )
I hope someone will help me soon.
Its urngent
thanks in advance
regards
Waqas
View 10 Replies
View Related
Sep 2, 2014
When attempting to pull this query I get this error: "Each GROUP BY expression must contain at least one column that is not an outer reference."
View 1 Replies
View Related