Problem With SELECT, GROUP BY And Aggregate Function
Oct 24, 2006
Hi all,
I have a problem with an SQL-query and I don't know what the best solution would be to solve the problem.
/*INSERT INTO WERKS (
WERKS.Z8601,
WERKS.Z8602,
WERKS.Z8603,
WERKS.Z8604,
WERKS.Z8605,
WERKS.Z8606,
WERKS.Z8607,
WERKS.Z8608,
WERKS.Z8609,
WERKS.Z8610,
WERKS.Z8611,
WERKS.Z8621,
WERKS.Z8622,
WERKS.Z8623,
WERKS.Z8624,
WERKS.Z8625,
WERKS.Z8626,
WERKS.Z8627,
WERKS.Z8628,
WERKS.Z8629,
WERKS.Z8630,
WERKS.Z8631,
WERKS.Z8632) */
SELECT
0,
Stati.z4414,
Stati.z4402,
'',
'',
'',
Isnull((select Srtas.z02 from Srtas where Srtas.z00 = Stati.z4400 and Srtas.z01 = Stati.z4414), ''),
Isnull((select Klant.z0102 From Klant where Klant.z0101 = Stati.z4402), ''),
'',
'',
'',
sum (Case when Stati.z4407 = 200609 then Stati.z4409 Else 0 End),
sum (Case when Stati.z4407 = 200609 then Stati.z4410 Else 0 End),
sum (Case when Stati.z4407 = 200509 then Stati.z4409 Else 0 End),
sum (Case when Stati.z4407 = 200509 then Stati.z4410 Else 0 End),
sum (Case when Stati.z4407 Between 200510 and 200609 then Stati.z4409 Else 0 End),
sum (Case when Stati.z4407 Between 200510 and 200609 then Stati.z4410 Else 0 End),
sum (Case when Stati.z4407 Between 200410 and 200509 then Stati.z4409 Else 0 End),
sum (Case when Stati.z4407 Between 200410 and 200509 then Stati.z4410 Else 0 End),
sum (Case when Stati.z4407 = 200609 then Stati.z4411 Else 0 End),
sum (Case when Stati.z4407 = 200509 then Stati.z4411 Else 0 End),
sum (Case when Stati.z4407 Between 200510 and 200609 then Stati.z4411 Else 0 End),
sum (Case when Stati.z4407 Between 200410 and 200509 then Stati.z4411 Else 0 End)
FROM STATI
WHERE
(Stati.z4402 Between '40000' AND 'ZONE6') AND
(Stati.z4414 Between '2005028' AND '2005028') AND
(Stati.z4417 = 'A')
GROUP BY Stati.z4414, Stati.z4402
I get the following error:
Msg 8120, Level 16, State 1, Line 25
Column 'STATI.Z4400' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
I know it has something todo with the select on the table SRTAS, but what's the best way to solve this problem without the chance of getting a wrong result.
The SELECT on SRTAS is to get the "description" of STATI.Z4414 who's stored in the table SRTAS.
I only want to group on the fields that will be inserted in WERKS.Z8602, WERKS.Z8603, WERKS.Z8604, WERKS.Z8605, WERKS.Z8606. So adding STATI.Z4400 to the GROUP BY would give me wrong results?
This query is dynamicly generated from within my program from what the user selected.
Also, if there are better ways to write the query, I would be happy to get some hints and tips, but if possible without stored procedures.
Thanks in advance!
View 5 Replies
ADVERTISEMENT
Aug 7, 2007
Hello,
I column that calculated at run time in insert , can i gruop by this column,the new one that not exist yet
View 6 Replies
View Related
Dec 23, 2014
I have a query that I need to group by by using the aggregate function using MSSQL...
I want to be able to group the following columns into ONE line:
AddedDescription, OurRef, MaterialName, StockFamily, DateCreated, WJCStatusID AND THEN have WeightToSend Summed for all lines that are grouped above.
View 2 Replies
View Related
Jan 22, 2004
I'm trying to update a varchar field using SUM. I keep getting the error that the sub query returns more than one value.
UPDATE CIRSUB_M
SET TRM_DMO = SUBSTRING(TRM_DMO,1,11) +
(SELECT CAST(SUM(COPIES) AS VARCHAR(5)) FROM CIRSUB_M
WHERE BIL_ORG = '02' AND CRC_STS IN ('R','P','Q','T')
GROUP BY PUB_CDE, DNR_NBR)
WHERE BIL_ORG = '02' AND CRC_STS IN ('R','P','Q','T')
Example
PUB_CDE DNR_NBR COPIES TRM_DMO
THN 000000092637 100 A
THN 000000092637 200 B
THN 000000082455 100 A
THN 000000082455 200 B
THN 000000051779 100 A
Updated
THN 000000092637 100 A300
THN 000000092637 200 B300
THN 000000082455 100 A300
THN 000000082455 200 B300
THN 000000051779 100 A100
View 4 Replies
View Related
Aug 7, 2015
Well adding it to a group by or function skews the result set. How to write this query so it displays as I need it to? This is what I have thus far, and it works as it should UNTIL I add in the line of
cast(cte.[C] As float)/cast(sum(cte.[C]) over() as float)*100 As [Rate1],
Presents the error of:
Msg 8120, Level 16, State 1, Line 35
Column 'cte.[C]' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
This is my full on query -- with 3 CTE's involved to get me the actual result set I am after.
;with cte as
(
select
[state],
case when exists (select 1 from table2 R where R.centername = d.centername) then 1 else 0 end as [L],
case when exists (select 1 from table3 C where C.centername = d.centername) then 1 else 0 end as [C]
FROM maintable d
),
[Code] .....
View 4 Replies
View Related
Nov 29, 2011
best solution for this stored procedure query.I'm getting the following error:
Column 'dbo.Applicants.submitted' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause. Here is my select query:
Code:
SELECT DISTINCT DATENAME(MONTH, submitted) AS mon, MAX(submitted) AS SubmitDate
FROM dbo.Applicants
WHERE ((CASE WHEN MONTH(submitted) > 8 THEN YEAR(submitted) + 1 ELSE YEAR(submitted) END) = @AcYr)
ORDER BY SubmitDate
The submitted field is a date field.I don't want to add the submitted field to Group By as I want to group by month not date.Is there any solution to avoid grouping by date?
View 3 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
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
Apr 11, 2008
I have a table like this below:
<PRE>
Page Book Release MaxPages
1234ABC A1
1234ABC B2
9999ABC D1
9999ABC E2
7777ABC A1
7777ABC C2
</PRE>
I want to select every page of the book, but only the highest release of that page.
Something Like:
Select Book, Page, Max(Release), MaxPages
From Table
But I can't quiet figure it out.
Thanks for any help.
View 11 Replies
View Related
Apr 16, 2008
Can you provide me a way to avoid using the aggregate function Max in the following select statement?
SELECT a.clmssn,
a.cossn,
Max(b.clm_seq_num) as clm_seq_num,
Max(c.chrs_seq_num) as chrs_seq_num,
Max(b.clm_dcn_dt) as clm_dcn_dt
FROM Claim a
LEFT OUTER JOIN clmdcn b
ON a.clmssn = b.clmssn
AND a.cossn = b.cossn
AND a.clm_seq_num = b.clm_seq_num
INNER JOIN clmchrs c
ON a.clmssn = c.clmssn
AND a.cossn = c.cossn
AND a.clm_seq_num = c.clm_seq_num
Group By a.clmssn, a.cossn
Order By a.clmssn, a.cossn
I want to join another table, Juris, to get the Crnt_Office column but the aggregate function Max forces me to include Crnt_Office it in the group by clause. This results in returning all rows containing unique office codes. I only want the office code from the row with the highest Clm_Seq_Num and Juris_Seq_Num.
Juris
Clmssn = key
Cossn = key
Clm_seq_num = key
Juris_seq_num = key
Crnt_Office
The following returns incorrect results:
SELECT a.clmssn,
a.cossn,
Max(a.clm_seq_num) as clm_seq_num,
Max(c.chrs_seq_num) as chrs_seq_num,
Max(d.juris_seq_num) as juris_seq_num,
Max(b.clm_dcn_dt) as clm_dcn_dt,
d.crnt_office
FROM mt16ic_Claim a
LEFT OUTER JOIN mt16ic_clmdcn b
ON a.clmssn = b.clmssn
AND a.cossn = b.cossn
AND a.clm_seq_num = b.clm_seq_num
LEFT OUTER JOIN mt16ic_juris d
ON a.clmssn = d.clmssn
AND a.cossn = d.cossn
AND a.clm_seq_num = d.clm_seq_num
INNER JOIN mt16ic_clmchrs c
ON a.clmssn = c.clmssn
AND a.cossn = c.cossn
AND a.clm_seq_num = c.clm_seq_num
Group By a.clmssn, a.cossn, d.crnt_office
Order By a.clmssn, a.cossn
If more information is needed, I have data examples but could not include them due to character size limit. Thank you for your help.
View 12 Replies
View Related
Oct 19, 2004
Ok, for a bunch of cleanup that i am doing with one of my Portal Modules, i need to do some pretty wikid conversions from multi-view/stored procedure calls and put them in less spid calls.
currently, we have a web graph that is hitting the sql server some 60+ times with data queries, and lets just say, thats not good. so far i have every bit of data that i need in a pretty complex sql call, now there is only one thing left to do.
Problem:
i need to call an aggregate count on the results of another aggregate function (sum) with a group by.
*ex: select count(select sum(Sales) from ActSales Group by SalesDate) from ActSales
This is seriously hurting me, because from everything i have tried, i keep getting an error at the second select in that statement. is there anotherway without using views or stored procedures to do this? i want to imbed this into my mega sql statement so i am only hitting the server up with one spid.
thanks,
Tom Anderson
Software Engineer
Custom Business Solutions
View 3 Replies
View Related
Aug 14, 2006
Hi,
Could some one help me how to do
insert into test2(id,name) values ((select max(id) from test1),'user1')
in MS SQL Server
its throwing "Subqueries are not allowed in this context. Only sc
alar expressions are allowed" Exception. Help is appreciated.
Thanks,
Murali
View 2 Replies
View Related
May 26, 2015
I tend to learn from example and am used to powershell. If for instance in powershell I wanted to get-something and store it in a variable I could, then use it again in the same code. In this example of a table order items where there are order_num, quantity and item_prices how could I declare ordertotal as a variable then instead of repeating it again at "having sum", instead use the variable in its place?
Any example of such a use of a variable that still lets me select the order_num, ordertotal and group them etc? I hope to simply replace in the "having section" the agg function with "ordertotal" which bombs out.
select order_num, sum(quantity*item_price) as ordertotal
from orderitems
group by order_num
having sum(quantity*item_price) >=50
order by ordertotal;
View 11 Replies
View Related
Apr 3, 2015
I have a specific variation on the standard 'Column Invalid' question: I have this query that works fine:
SELECT vd.Question ,
csq.Q# ,
csq.Q_Sort ,
csq.Q_SubSort ,
AVG(CAST(vd.Response AS FLOAT)) AS AvgC ,
vd.RType
[Code] ....
When I add this second average column like this:
SELECT vd.Question ,
csq.Q# ,
csq.Q_Sort ,
csq.Q_SubSort ,
AVG(CAST(vd.Response AS FLOAT)) AS AvgC ,
[Code] ....
I get the error: Column 'dbo.vwData.Response' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Clearly things are in the right place before the change, so I can only assume that the OVER clause is my problem. Is this just not possible?
View 1 Replies
View Related
Oct 19, 2007
Can any1 tell me why i am getting an error
SELECT DISTINCT
--p.voucher,
--p.amount,
p.siteID,
b.siteID,
SUM((round(b.total,2,2)) - SUM(round(p.amount,2,2))) AS OutStandingBalance,
SUM((round(b.total,2,2)) - SUM(round(p.amount,2,2))) AS CashCheque,
SUM((round(b.total,2,2)) - SUM(round(p.amount,2,2))) AS Vouchers
FROM
BillingTotal b,
Payment p
--WHERE
-- s.sitename=@cmb1
--AND p.siteid = s.siteid
-- p.voucher = 0
-- p.voucher = 1
GROUP BY p.siteID,b.siteID
Msg 130, Level 15, State 1, Line 1
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
View 8 Replies
View Related
Apr 4, 2007
Hi,
I'm trying to use the aggregate transformation to sum my orders table unit price and quantity with a grouping of state but i can't see how to add the sub grouping. My order table has the following fields of interest Unit Price (Money), Quantity (Integer) and State (Varchar)
ID
Unit Price
Quantity
State
1
$2.19
500
AZ
2
$29.99
33
WA
3
$1000.00
1
WA
4
$1.20
7
WA
When i run the aggregate i want the output to be grouped by state
Total Price
Quantity Sold
State
$2.19
500
AZ
$1031.19
41
WA
Hope the values are correct
Martin
View 7 Replies
View Related
Jul 20, 2005
Hello, everyone!Does anyone know how I can pull additional field in a database whenthe max() of one field is pulled. For example:================================================== ===SELECT TOP 200 foreign_id, MAX(recordcreateddatetime) ASmax_recordcreateddatetimeFROM table1GROUP BY foreign_id================================================== ===Here I am trying to pull the records that have the latest date foreach foreign_id. The result set above will pull foreign_id andmax_recordcreateddatetime but I needed to also have it display onemore field, current_status like this:================================================== ===SELECT TOP 200 foreign_id, MAX(recordcreateddatetime) ASmax_recordcreateddatetime, current_statusFROM table1GROUP BY foreign_id================================================== ===The problem however is that SQL wants me to add this field to GROUP BYor use an aggregate function with it and I don't want any aggregatefunction processing - I just want current_status to show up there forme to see. How do I do this?Thank you for any input in advance!Roumen.
View 3 Replies
View Related
Jan 16, 2014
Installed the Northwind database for data to practice with. I'm trying to combine data from several tables to make the orders table more readable. Meaning, I'm trying to replace the EmployeeID field with the combination of the firstname and lastname fields from the Employees table. Everything works fine until I try to sum the Unit price field from the [Order Details] table. Using just a SUM() function or the Select statement below causes the error and any combination of fields in the Group By command don't correct it. It's clear that I'm doing something wrong, I'm just not sure how to get the data I want or use the group by command properly. Query below:
Select o.OrderID, c.companyName, e.firstname + ' ' + e.lastname EmployeeName, o.orderdate, s.companyName,
o.Freight, o.shipName, o.ShipAddress, (Select Sum(od.UnitPrice) from [Order Details] od where od.OrderID = o.OrderID)as Amount
from orders o, customers c, Employees e, Shippers s, [Order Details] od
where o.CustomerID = c.CustomerID
[Code] ....
Running the first query (with the select statement) works, but returns a row for each of the the items that was ordered for that OrderID and NOT using the Group By. I would like to have the SUM() of the items ordered in one row. Is this possible?
View 6 Replies
View Related
Aug 5, 2015
How can I aggregate this result into 1 row? (I got it from a UNION ALL)
Article Assort1 Assort2
50095811 K1 NULL
50095811 NULL K3
I would like to have
Article Assort1 Assort2
50095811 K1 K3
View 3 Replies
View Related
Feb 21, 2008
Does anyone know how to make a query and use an aggregate function? This is my current code...any help would be great.
"SELECT tblTopic.Topic_ID, tblTopic.Subject, MAX(tblThread.Message_date) AS MessageDate, tblThread.Message FROM (tblThread INNER JOIN tblTopic ON tblThread.Topic_ID = tblTopic.Topic_ID) WHERE (tblThread.Message_Date LIKE '%' + @fldGenus + '%' GROUP BY tblTopic.Topic_ID, tblTopic.Subject, tblThread.Message">
Also, How can i limit the query to only bringing up 5 records?
I'm trying to get a datagrid to show the 5 most recent forum posts for a particular category.
Thanks.
View 2 Replies
View Related
Mar 9, 2007
Hello, I need to use last() aggregate function in MS SQL Server 2005 but it is not built in.How to replace this functionality?Thanks.
View 4 Replies
View Related
Apr 3, 2007
I have three tables, tblschedule, tblresource and tblemployeename.
in tblschedule table there are scheduleID, resourceID and employeeID. In tblResource there are ResourceID and ResourceName. In tblemployeename there are EmployeeID, EmployeeFName and EmployeeLame. I want to have a report that show how many times the resource has been reserved by employee. i would like to have a report. Look like the following:
ResourceName EmployeeFName EmployeeLName (Or use EmployeeName) Number of record.
How to write a query? I use asp.net as front end.
Thank for your help.
View 1 Replies
View Related
Mar 4, 2004
Hi
I have a query where I need to use an aggregate function MAX in where clause, I know that MAX cannot be used in a Where clause because it is an aggregate function. Can anyone help me out in writing this query?
SELECT * FROM ACCOUNT_REVIEW AR INNER JOIN
QUESTION_RESPONSE ON
AR.Review_ID = QUESTION_RESPONSE.Review_ID
WHERE (MAX(AR.Review_Date) IS NULL)
I need it asap. Thanks in advance,
K
View 6 Replies
View Related
Feb 17, 2008
Hi Guys,
I am having trouble with a particular query that is beyond my scope of understanding.
Basically I need to pull sales records based on the following criteria:
I have CustomerID, InvoiceNumber, ContractEndDate, MobileNumber, etc..
Customers recontract their mobile phone plans through us, and we have a new sales record for each time they recontract.
For example, CustomerNumber 123 has recontracted 3 times..
once on 2006-01-01, then on 2007-02-12, and finally on 2008-02-15..
So they have a 12 month contract each time.. then come in to recontract it.
So.. a customer has a single Customer Detail record, but may have many sales records attached. And a customer may have several sales for the SAME mobile phone number.
Currently to pull ALL sales records for all customers, my query is this:
Code:
SELECT xxx.CustomerID AS xxx_CustomerID,
xxx.Invoice AS xxx_Invoice,
yyy.PhoneType AS yyy_PhoneType,
yyy.PlanType AS yyy_PlanType,
yyy.ContractEnds AS yyy_ContractEnds,
yyy.MOB AS yyy_MobileNumber
FROM dbo.SaleControl xxx INNER JOIN dbo.SaleDetails yyy ON xxx.Invoice = yyy.Invoice
WHERE yyy.ContractEnds IS NOT NULL
AND xxx.CustomerID IS NOT NULL
We want to get a list of customers that we can call to recontract, based on the ContractEnd field.
However, we want UNIQUE mobile phone numbers, with the LATEST ContrtactEnd date.
So, Customer 123 has 6 sales, for 2 unique Mobile numbers, the sql may be like:
Code:
SELECT MAX(yyy.ContractEnds) AS LatestCED, yyy.MOB
FROM dbo.SaleControl xxx INNER JOIN dbo.SaleDetails yyy ON xxx.Invoice = yyy.Invoice
WHERE xxx.CustomerID='123'
GROUP BY yyy.MOB
Now, this works fine, and of course if i remove the WHERE clause, it collects all unique mobiles, with latest ContractEnd date for each, for all customers. (Customer 123 displays 2 mobile numbers, each with the LATEST ContractEnd date)
BUT i need this information ALONG WITH the other fields (xxx.CustomerID, xxx.Invoice, yyy.PhoneType, yyy.PlanType) and i have tried a few ways of doing it, but can't get my head around it..
Keep getting errors about Aggregate functions and Group By clause, and i understand why i am getting them, just cant think of any alternative query.
Can anyone please help me!
Thanks guys,
Mick
View 1 Replies
View Related
Jul 16, 2015
I am using SQL 2005. I have some data from an old application that did not follow the rules for normalization. The table is for Invoices, and the table allows for 13 purchase items per record. So in each row of my table I have a non-unique integer field itemID, itemID1, itemID2 ... itemID12. For each itemID I also have "lbs_total" and "line_total" (which is price * lbs_total) - so itemID, lbs_total, line_total ... itemID1, lbs_total1, line_total1 ... etc. It's a mess, I know.Each row has a unique Customer Number ("cno") and an Invoice Date ("inv_date"). My proc needs to allow for params for the item number, and a start date and end date for BETWEEN on the inv_date.I also need to get the aggregate for the lbs_total and the line_total.
View 15 Replies
View Related
Mar 19, 2006
Hey everyone, I'm looking for a way to insert a count of a set of records into a table and then read that table and produce an XML file from that dataset. The problem that I'm coming across is that when you do this type of query you have to specify one of the values from the table and then get a count for the number of records of that particular range of values.
The table that I am trying to get the data from will look something like this:
ItemID ModuleID Description ......1 2869 blah blah blah2 2869 blah blah blah3 2869 blah blah blah4 2690 bit bit bit5 2690 bit bit bit6 2690 bit bit bit
So I count the 2869's and 2690's and place the ModuleID and the count of the ItemID's in another table.
Then I need to extract the data from the table and put it into an XML file that is readable by the ReadXML method in ASP.NET.
So far I've got the SPROC to do only one record at a time but I would like one that does the whole table rather than one at a time using a parameter.
View 5 Replies
View Related
Mar 8, 2004
I am trying to write an update statement based on an aggregate and it will not let me. Please find below the SQL.
update abtimesummary
set hours = sum(a.hours)
from abtimestore a
join abtimesummary b
on (cast(a.weekno as varchar(10)))+'-'+(cast(a.empno as varchar(10))) = b.summaryid
and this is the error message:
Server: Msg 157, Level 15, State 1, Line 2
An aggregate may not appear in the set list of an UPDATE statement.
Can someone tell me how to get round this please?
Many thanks
View 2 Replies
View Related
Jun 15, 2004
hi there,
I have a query that works on sybase and want to make it also works on SQL Server.
The problem is that in this query I 'm using a subquery in an aggregate function.
It seems that SQL Server unlike Sybase doesn't support the use of subquery in aggregate function.
How can I overcome this problem.
Thanks,
Fady
View 3 Replies
View Related
Jan 20, 2006
I am working on a view in SQL Server 2005.
I am trying to get a list of the number of sessions each user had by user. I tried doing it this way, but
SELECT userid, MAX
((SELECT COUNT(DISTINCT sessionId) AS SESSIONCOUNT
FROM dbo.Sessions AS OD
HAVING (sessionId = O.sessionId))) AS MAXSESSION
FROM dbo.Sessions AS O
GROUP BY userid
but it throws an error 'Cannot perform an aggregate function on an expression containing an aggregate or subquery.'
Is there an elegant solution for this?
Thanks,
View 1 Replies
View Related
Feb 6, 2014
If you use Coalesce with Count will it return all values even when null? Reason I pose this question to you is that I am running the below query and am getting escalated results than what should be returned. 1st stop on the debug train is how does Coalesce handle it....for example Boston should return only 143 but the query retunrs 194 for Boston?
Code:
Select
Count(NumOnsite),
originatingCity,
Coalesce(Convert(varchar(4000),NewspaperNames), Convert(varchar(4000),MagazineNames)) As PaperNames
From readytoshipOffsite
Group By originatingCity, Coalesce(Convert(varchar(4000),NewspaperNames), Convert(varchar(4000),MagazineNames))
View 11 Replies
View Related
Jul 2, 2015
Why aren't we getting proper MAX(DOJ) value in Weird_Maxgroup Column in the below query ? Similar thing happens with MIN() function as well.
Is there something that I am missing here ?
;WITH CTE AS
(
SELECT 1 AS ID, 10000 AS SALARY,GETDATE() AS DOJ
UNION ALL
SELECT 1, 13000,GETDATE() - 10
UNION ALL
SELECT 3, 190000,GETDATE() + 10
[Code] ....
View 9 Replies
View Related
May 6, 2008
Hello,
I want to count how many occurences their is of each date that is returned by my sql query. I am not sure how to add the aggregate function code to my query I know how to just tell it to count all records, but not to tell it to count for each group of dates. For example I want it to count how many times 5/6/08 shows up in the returned results and so on. Here is my query I currently have. Any help would be greatly appreciated! Thanks!
The enc_timestamp is my date field.
Select a.template_id, a.enc_timestamp, a.created_by, b.first_name, b.last_name, b.last_name +', ' + b.first_name as fullname
From template_audit a
Join user_mstr b on a.created_by = b.user_id
GROUP BY a.template_id, a.enc_timestamp, a.created_by,b.first_name, b.last_name
Having a.template_id IN (543,3172,3031,3030,3134,3135,3171,1401,1937,3985,3173,2320,57,849,1775,1400,1747,3695,3957,3750,3954,3027,3241)
ORDER BY a.enc_timestamp, b.first_name, b.last_name;
Thanks in advance,
Sherri
View 17 Replies
View Related
May 22, 2008
Hi guys,
Does anybody know a convenient way to return the product of a column, similar to something like the 'sum' function?
If I have a table of form id-date-value, I'd like to do something like:
select id, date, product(val)
If not, does anyone know of a way one could structure this as a subquery?
If all else fails, I suppose I'll have to resort to a loop or cursor, but I'd rather not.
Any help would be much appreciated.
View 11 Replies
View Related
