Group Or Max Date Function?
May 21, 2008
I am creating a query in MS Access. My table looks like this:
Wrk, Sample, Y
0805393, 01, 5/16/08
0805393, 02, 5/16/08
0805393, 03, 5/16/08
0805393, 03, 5/17/08
0805393, 03, 5/17/08
How can I group my data to show up like the following? I need to get the wr, sample and the most current date in MS Access.
0805393, 01, 5/16/08
0805393, 02, 5/16/08
0805393, 03, 5/17/08
I tried Max(Y), and also grouping them and it's driving me crazy!... Thanks
View 5 Replies
ADVERTISEMENT
Feb 12, 2015
I have a code like this:
SELECT Node_ID,Day,Operation, AA,BB
FROM
(SELECT
CASE
WHEN Operation LIKE 'NOTIFY' THEN SUM(Total_request) ELSE 0 END AS AA,
CASE WHEN OPERATION LIKE 'SEARCH' THEN SUM(Total_requests) ELSE 0 END AS BB,Node_ID,DAY,Operation
[code]....
So i want to make two columns by the name of operation. in the real code AA and BB are calculates with many counters. My code doesn't work, I have an error: "not a single-group group function" .....
View 1 Replies
View Related
Jan 29, 2007
I'm new to MSSQL 2005 and want to get a summary of a log table. I want to count all the rows for each date based on a DATETIME field called 'post_date' that holds the date and time of each record's creation.
this is the best I can come up with:
Code:
SELECT
DISTINCT(LEFT(post_date,11)) AS post_date, COUNT(DISTINCT(LEFT(post_date,11))) AS total_posts
FROM log_directory_contacts
GROUP BY post_date
The results show each date but the count column ('total_posts') returns '1' for every row even when I know their are more than 1 record on that date.
What am I doing wrong? Thanks!
View 9 Replies
View Related
Jul 29, 2015
My goal is to select values from the same date range for a month on month view to compare values month over month. I've tried using the date trunc function but I'm not sure what the best way to attack this is. My thoughts are I need to somehow select first day of every month + interval 'x days' (but I don't know the syntax).In other words, I want to see
Select
Jan 1- 23rd
feb 1-23rd
march 1-23rd
april 1-23rd
,value
from
table
View 9 Replies
View Related
Mar 18, 2014
I have the following
Column Name : [Converted Date]
Data Type : varchar(50)
When I try and do month around the [Converted Date] I get the following error message
“Msg 241, Level 16, State 1, Line 2
Conversion failed when converting date and/or time from character string.”
My Query is
SELECT
month([Created Date])
FROM [FDMS_PartnerReporting].[Staging].[Salesforce_MarketingReporting]
View 7 Replies
View Related
Mar 10, 2008
I have a reference table that currently has no web front-end. It's a small table(<10 rows) that's not going to change very often (maybe once every few months).
We manually update rows on the table via the GUI table interface in Enterprise Mgr., not in T-SQL.
What I'd like to do is have SQL Server automatically update the "Last_Modified" column with the current timestamp. I can do it on an Insert using the GetDate() function, but if I update a row, this doesn't work.
Is there a function I can use that can auto-populate for both insert and updates?
View 4 Replies
View Related
Nov 6, 2008
I need to group some student data by student id with each student's max test score. Here is the data:
Student A1 10/31/08 100
Student A1 11/06/08 99
Student Z2 10/31/08 75
Student Z2 11/06/08 88
I want to know for each student the max test score and what date that was achieved.
View 3 Replies
View Related
Mar 27, 2008
how do i use group by for the below query, am getting duplicate results for the query i execute, am not sure how do i use group by for this. pls.help.
SELECT
C.USER_ID,
A.comm_Name,
B.UU_TITLE,
FILENAME_URL=(Case When UU_Type = 'U' Then UU_url else UU_Filename End),
C.User_FirstName + ' ' + C.User_LastName AS Author,
Convert(CHAR(10),UU_TimeStamp,101) as DateCreated,
Convert(CHAR(10),UU_LastModified,101) as DateModified,
uu_AssocAuID,
DOWNLOADS = (select count(*) from Vportaldevstats.[dbo].stats_download A where A.stat_AuID = b.UU_AssocAuID )
FROM vportaldev.dbo.community A
INNER JOIN vportaldev.[dbo].user_upload B
ON A.comm_CreatorUserID = B.UU_USERID
INNER JOIN vportaldev.[dbo].[user] C
ON C.user_id = B.UU_USERID
thaks in advance
View 1 Replies
View Related
Apr 24, 2008
Hi All,
I would like to know if that the SUM() function can use more than 1 group in the scope portion. Example is as below:
Sum(Fields.Hour.Value, ("grp1", "grp2"))
This would help as I am doing a report using a matrix report. I need to do a calculation in the measure area so that it will calculate base on the row and column grouping. Is this possible?
Thanks & Regards,
Fadzli
View 3 Replies
View Related
Mar 19, 2007
can anyone tell me what is the use of group by in rs tables and how to use group by in tables (when we have to use )...if possible please gimme some examples..
View 1 Replies
View Related
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
Oct 26, 2012
I want to run a much larget SQL statement, but for examples sake this is a good starting point
Code:
Select efName, elName, eAddress, SUM(Convert(money, bonus1)+Convert(money, bonus2)+Convert(money, bonus3)) As TotalBonus, ePay FROM tableEInfo
It is telling me that I have to use Group By, but the problem is that most of my fields are text fields, which it looks like have to be converted in order to use with a group by statement. Is it possible to use the sum function with no group by statement?
View 13 Replies
View Related
Jun 19, 2014
select empid,orderdate from [Sales].[Orders] WHERE custid = 71
group by empid,orderdate
sample data resultset:
empid orderdate
1 2006-12-25
1 2007-12-24
1 2007-12-23
1 2008-12-23
1 2008-04-24
select empid,year(orderdate) from [Sales].[Orders] WHERE custid = 71
group by empid,year(orderdate)
sample data resultset:
empid orderdate
1 2006
1 2007
1 2008
why is the year() gets only one year when worked with groupby. when empid =1 the year 2007 has 2 records and 2008 has 2 records in first select and when I used year(orderdate) in secong select only one year for each is selected.
View 3 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
Feb 28, 2008
i have a query
select mydate,lastname from users
group by mydate,lastname
now the only thing is mydate is a datetime fields but i want the results to group by just the date for each lastname entered.
How do i do this?
View 3 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
Apr 24, 2008
select bicycle_shop.bicycle_shop_id, bicycle_shop.company_name, order_.unit_price
from order_ inner join bicycle_shop on order_.order_serial_number = bicycle_shop.order_serial_number
order by order_.unit_price DESC;
this query will bring up a few records for each shop, of the one or more bikes they bought and each bike's price, how do i have these come out to a sum(of the price), so that each bike shop will have a sum of revenue. someone told me i need to use group by and the sum function but i dont know how
View 5 Replies
View Related
May 16, 2008
Am I correct in assuming, in partitioning scenarion - the NUMBER OF PARAMETERS in a partitioning function is equal to number of file groups?
------------------------
I think, therefore I am - Rene Descartes
View 8 Replies
View Related
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
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
Mar 10, 2014
Consider the following dataset:
COL1 | COL2 | COL3 | COL4
1 | FD | DR. A | Y
2 | FD | DR. A | Y
3 | FD | DR. A | N
4 | FD | DR. A | Y
5 | FD | DR. A | Y
6 | PF | DR. A | Y
7 | FD | DR. B | Y
8 | PF | DR. B | N
Consider the script below:
SELECT
COL2, COL3, COUNT(COL1) AS TOTALS
FROM CASES
GROUP BY COL2, COL3
ORDER BY COL3, COL2
The script above produces the following output:
COL2 | COL3 | TOTALS
FD | DR. A | 5
PF | DR. A | 1
FD | DR. B | 1
PF | DR. B | 1
I need to add one more column to the script that counts records with 'Y' in COL4 for each COL1 category (FD, PF). The final dataset would look like this:
COL2 | COL3 | TOTALS | NEWCOL
FD | DR. A | 5 | 4
PF | DR. A | 1 | 1
FD | DR. B | 1 | 1
PF | DR. B | 1 | 0
I am having a hard time trying to use COUNT() on multiple columns with the GROUP BY restrictions that exist.
View 2 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
Jun 8, 2012
For code reuse, I am trying to get a table valued function to return users of a given AD group name. I can easily get this with hard-coding the group name. But because OpenQuery wont accept parameters, I can't insert my group name there. And because functions can't call dynamic SQL, I can't do it via dynamic sql. I have seen people do it with CLR, but I rather not go that route. I can use a stored procedure + cursor and iterate through each group and store the results into real tables and create a cache, but I rather query Active Directory itself to save space, but I rather do the caching then the CLR. Any approach I am missing on how to do this?
The following works fine:
SELECT DISTINCT sAMAccountName
FROM OPENQUERY(ADSI, 'SELECT sAMAccountName, sn
FROM ''LDAP://OU=SomeOU,OU=SomeOtherOU,DC=SomeDC,DC=SomeOtherDC''
WHERE objectCategory=''Person'' AND objectClass=''USER'' AND memberOf=''CN=SomeGroupName,OU=SomeOU,OU=SomeOtherOU,DC=SomeDC,DC=SomeOtherDC''') a
WHERE sn IS NOT NULL
The following gives me the error:
Invalid use of a side-effecting operator 'EXECUTE STRING' within a function.
CREATE FUNCTION [dbo].queryADGroupMembers
(
@group nvarchar(255)
)
RETURNS @rtnTable TABLE
[Code] .....
View 7 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 14, 2006
I have the following fields in table A:
GL_ID|GL_Name_VC| Amount |Period_TI|Year_SI|
==========================================
1000| Software| 2,000.00 | 01 | 2005
==========================================
1000| Software| 2,000.00 | 01 | 2005
==========================================
1000| Software| 5,000.00 | 02 | 2005
==========================================
1000| Software| 5,000.00 | 02 | 2005
==========================================
1001| Hardware| 10,000.00 | 02 | 2005
the fields above have the following datatype:
Fields | Datatype
===================================
GL_ID | Integer
GL_Name_VC | Variable Character
Amount | Integer
Period_TI | TinyInteger
Year_SI | SmallInteger
The above database is running on Microsoft SQL Server 2000 and i would like to query
for a report that looks something as below:
Description | Hardware | Software | Total
=======================================
Opening Balance | 0.00 | 4,000.00 | 4,000.00
Period 02 | 10,000.00 | 10,000.00 |20,000.00
=======================================
Closing Balance | 10,000.00 | 14,000.00 |24,000.00
The above report has 4 columns, with 1st & last column being a calculated field.
Notice that the Opening Balance description is a sum of all amount under period 01,with software having a total Opening Balance of 4,000.00 (2,000.00 + 2,000.00).
The amount for both Hardware & software are group by period 02, and a Closing Balance is derived by adding the sum of Period 01 & Period 02.Total column is a summation of
both Hardware & Software amount.
Guys, hope someone out there can help me with the sql command for the above report?
View 4 Replies
View Related
Apr 11, 2006
I have the following fields in table A:
GL_ID|GL_Name_VC| Amount |Period_TI|Year_SI|
===================================================
1000| Software| 2,000.00 | 01 | 2005
===================================================
1000| Software| 2,000.00 | 01 | 2005
===================================================
1000| Software| 5,000.00 | 02 | 2005
===================================================
1000| Software| 5,000.00 | 02 | 2005
==================================================
1001| Hardware| 10,000.00 | 02 | 2005
the fields above have the following datatype:
Fields | Datatype
===================================
GL_ID | Integer
GL_Name_VC | Variable Character
Amount | Integer
Period_TI | TinyInteger
Year_SI | SmallInteger
The above database is running on Microsoft SQL Server 2000 and i would like to query
for a report that looks something as below:
Description | Hardware | Software | Total
====================================================
Opening Balance | 0.00 | 4,000.00 | 4,000.00
Period 02 | 10,000.00 | 10,000.00 |20,000.00
====================================================
Closing Balance | 10,000.00 | 14,000.00 |24,000.00
The above report has 4 columns, with 1st & last column being a calculated field.
Notice that the Opening Balance description is a sum of all amount under period 01,
with software having a total Opening Balance of 4,000.00 (2,000.00 + 2,000.00).
The amount for both Hardware & software are group by period 02, and a Closing Balance
is derived by adding the sum of Period 01 & Period 02.Total column is a summation of
both Hardware & Software amount.
Guys, hope someone out there can help me with the sql command for the above report?
View 1 Replies
View Related
Jan 12, 2007
I have a table with a datetime field and a username. the following gives me a count of the individual users reagrdless of how many entries there are for them.
SELECT COUNT(DISTINCT Log_Username) AS Expr1
FROM tblUserLog
However, I also want to have a total of distinct users for each date - how do I do this ?
TIA
View 14 Replies
View Related
Feb 2, 2007
hi all..
my group by clause has to select date.. problem is it differentiate
2006-01-23 15:45:34.000 and 2006-01-23 15:46:55.000 ..
As we all know, for sure we need to group all the 23 jan date into one without bothering the time, so how do i let the query pick only one line for date 23 jan?? thankss in advance...
~~~Focus on problem, not solution~~~
View 2 Replies
View Related
Dec 5, 2007
Hello SQL Gurus,
Here is my challenge...
I have a database with a table that has a date field in numerical format, ie. 20071231 (YYYYMMDD).
the table lists Invoice numbers, their date, value, customerID, etc.
what would the query look like that shows the sum of value of invoices, for a customer, grouped by each month.
so a recordset that looks like
month, customerid, sum(invoicevalue)
200708, 113, $1000
200709, 113, $1111
200710, 113, $1010
View 12 Replies
View Related
Aug 6, 2013
I want to modify the following SQL Query to replace the hard-coded date with 5 years back from the current month (The Current Month minus 5 years).
SELECT FirstName, LastName, LatestHireDate, COUNT(*) as "Latest Hire Date"
FROM dbo.EMPLOYEE
WHERE LatestHireDate > '2013-08-01' and ActiveResourceFlag = 'TRUE'
GROUP BY LatestHireDate, FirstName, LastName;
View 7 Replies
View Related
Nov 3, 2006
Hi,I have a datetime column named dtDateTime.its format is "Oct 27 2006 12:00:00 "I want to group by only date part of it and countmy code is$sql1="SELECT convert(varchar,J1708Data.dtDateTime,120),count(convert(varchar,J1708Data.dtDateTime,120))FROM Vehicle INNER JOIN J1708Data ON Vehicle.iID = J1708Data.iVehicleIdWHERE (J1708Data.iPidId = 303) AND(J1708Date.dtDateTime between '2006-10-25' AND '2006-10-28')AND (Vehicle.sDescription = $VehicleID)GROUP BY convert(varchar,J1708Data.dtDateTime,120)";However, convert part, group by part doesnt' work at all.(i couldn't check count part)can you find where's the problem?Thx.
View 2 Replies
View Related
Sep 12, 2007
I am trying to to a transaction count (per day) running a stored procedure that does a group by date. The problem is that since the date has a time stamp (I assume) it sees each date as a different group even if it's the same day. Is there a way to format the date in the stored procedure so that it sees all of the transactions on the same day as one or is there another way to do this.
Select count(recordid),transactiondatefrom sometable group by transactiondate
Thanks
in advance
View 1 Replies
View Related
