Include ID Field In GROUP BY Statement
May 15, 2007
I've got a query where i need to return a max value based on a select but one of the fields i need to return in the results is the records primary key ID No. This messes up the MAX bit and means that all results are returned, not just the max one.
The query i'm using is very long so i've simplified what i mean by the example below. Say i have a table 'Fruits':
ID FruitName Cost
1 Apple 0.45
2 Apple 0.63
3 Apple 0.52
4 Pear 0.89
5 Pear 0.83
And run the query:
select max(Cost),FruitName From Fruits
group by FruitName
It'll correctly return:
FruitName Cost
Apple 0.63
Pear 0.89
Now i need the ID also returned by my query so i go:
select max(Cost),FruitName,ID From Fruits
group by FruitName,ID
This doesnt return the above results with the ID appended to it, it instead returns:
ID FruitName Cost
1 Apple 0.45
2 Apple 0.63
3 Apple 0.52
4 Pear 0.89
5 Pear 0.83
As the ID is always distinct and therefore messes up the grouping. How in this instance would i return the correct result of:
ID FruitName Cost
2 Apple 0.63
4 Pear 0.89
Thanks.
View 9 Replies
ADVERTISEMENT
May 20, 2004
When I created a CASE statement (This is at work, Pat:)) it is about 30-40 lines long. I gave it a name and set the name = to the case statement:
ie,
SELECT fieldname1 =
CASE
WHEN condition THEN 'blah blah'
WHEN condition THEN 'blah blah'
WHEN condition THEN 'blah blah'
ELSE thisandthat
END
, fieldname2
, fieldname3
FROM tablename1
GROUP BY CASE
WHEN condition THEN 'blah blah'
WHEN condition THEN 'blah blah'
WHEN condition THEN 'blah blah'
ELSE thisandthat
END, , fieldname2, fieldname3
etc.
The long CASE statement in my GROUP BY is awkward to me. Is this the only way to do it? I tried using the fieldname1 but it comes back as an invalid field name and asks for the "expression".
Regards,
Dave
View 5 Replies
View Related
Dec 3, 2007
hey all,
say i have the following function
SELECT GLF_CHART_ACCT.DESCR1, F1ADR_ADDRESS.ADDR1, F1ADR_ADDRESS.ADDR2,
F1ADR_ADDRESS.ADDR3, F1ADR_ADDRESS.ADDR_CITY, F1ADR_ADDRESS.ADDR_STATE,
F1ADR_ADDRESS.POST_CODE, F1ADR_ADDRESS.PHONE_NBR, F1ADR_ADDRESS.FAX_NBR,
F1ADR_ADDRESS.EMAIL_ADDR_NAME, F1ADR_ADDRESS.CONTACT_NAME,
F1ADR_ADDRESS.CONTACT_TITLE, GLF_CHART_ACCT.ACCNBRI, F1ADR_ADDRESS.ENTITY_UNIQUE_NBR
FROM GLF_CHART_ACCT
INNER JOIN F1ADR_ADDRESS ON (GLF_CHART_ACCT.CHART_NAME = F1ADR_ADDRESS.ENTITY_KEY1)
AND (GLF_CHART_ACCT.ACCNBRI = F1ADR_ADDRESS.ENTITY_KEY2)
GROUP BY GLF_CHART_ACCT.DESCR1, F1ADR_ADDRESS.ADDR1, F1ADR_ADDRESS.ADDR2,
F1ADR_ADDRESS.ADDR3, F1ADR_ADDRESS.ADDR_CITY, F1ADR_ADDRESS.ADDR_STATE,
F1ADR_ADDRESS.POST_CODE, F1ADR_ADDRESS.PHONE_NBR, F1ADR_ADDRESS.FAX_NBR,
F1ADR_ADDRESS.EMAIL_ADDR_NAME, F1ADR_ADDRESS.CONTACT_NAME, GLF_CHART_ACCT.ACCNBRI,
F1ADR_ADDRESS.CONTACT_TITLE, GLF_CHART_ACCT.CHART_NAME, F1ADR_ADDRESS.ENTITY_UNIQUE_NBR,
GLF_CHART_ACCT.SELN_TYPE1_CODE
HAVING CHART_NAME='ARCHART' AND GLF_CHART_ACCT.DESCR1 <> '' AND GLF_CHART_ACCT.SELN_TYPE1_CODE = 'Trade'
AND GLF_CHART_ACCT.DESCR1 LIKE '%" + Search + "%' ORDER BY GLF_CHART_ACCT.DESCR1;
I get errors if not all the fields are included in the group by clause.
what i dont get is why i have to create seperate groups for this query...or am i reading it wrong??
Cheers,
Justin
View 5 Replies
View Related
Mar 11, 2008
I want to include a value from my result set in the header of my report but get the "You cannot reference a field value in the page header or footer" message. Here's why I want to do it:
The report uses three parameters: Date1, Date2 and Type. If Type is set to DATES the stored proc uses dates 1 and 2 as criteria to limit the returned records. Because Dates 1 and 2 are set in the report I can use the parameters collection to get those values and include them in the title of the report. If the Type paramter is set to MONTH, dates 1 and 2 are ignored and the stored proc determines what the dates are going to be using a UDF called from within the proc. The only way I could think of to get these calculated dates back from the proc was to insert them into the temp table it creates. Cheesy, I know... but I got the dates to the result set. Problem is I can't use them for what I need. So... to make a short question very long... how can I get the values in the result set into something I can use in the header or footer?
Mike Devenney
View 3 Replies
View Related
Mar 21, 2006
I wanted to create something like this:Select (FirstItem + "/" + SecondItem) AS Itembut I get error. Is there anything wrong with this code?P.S. I'm using mssql 2000
View 2 Replies
View Related
Jul 17, 2013
How do you include a word with an apostrophe in an SQL in statement without getting an error msg? For example this syntax:
where provname1 IN ('Children's')
With that syntax above, I would get an error message.
View 4 Replies
View Related
Oct 18, 2006
I want to do an update query like the following:UPDATE tblUserDetails SET DeploymentNameID = 102 WHERE (EmployeeNumber = @selectedusersparam)Is there some simple way to add the @selectedusersparam as value1,value2,value3 etc. or do I have to input it with this type of syntax:UPDATE dbo_tblUserDetails SET dbo_tblUserDetails.DeploymentNameID = 102WHERE (((dbo_tblUserDetails.EmployeeNumber)=value1 Or (dbo_tblUserDetails.EmployeeNumber)=value2));Help appreciated.Many thanks.
View 5 Replies
View Related
Apr 20, 2015
I have this update statement that works for one record. How do I write it to include multiple records at once. Please see sample below.
update
mklopt
set
FRMDAT =
'12/31/2014'
where
JOBCOD =
'PH14789'
I also want to include the following instead of running it one at a time
PH17523
PH17524
PH17525
PH17553
PH17555
PH17556
PH17557
PH17558
PH17571
PH17573
PH17574
PH17575
PH17576
PH17577
PH1757
View 9 Replies
View Related
Oct 29, 2007
Hi guys,
I have the query below (running okay):
Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
FROM myTables
WHERE Conditions are true
ORDER BY Field01
The results are just as I need:
Field01 Field02
------------- ----------------------
192473 8461760
192474 22810
Because other reasons. I need to modify that query to:
Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
INTO AuxiliaryTable
FROM myTables
WHERE Conditions are true
ORDER BY Field01
SELECT DISTINCT [Field02] FROM AuxTable
The the results are:
Field02
----------------------
22810
8461760
And what I need is (without showing any other field):
Field02
----------------------
8461760
22810
Is there any good suggestion?
Thanks in advance for any help,
Aldo.
View 3 Replies
View Related
Sep 4, 2007
Hi guys,
I need to get a column with the sum of the field "SUF" from table "JurnalTransMoves_1" when that field ("SUF") is ordered by the field "REFERENCE" from table "Stock", and Show the value only once.
The desired result should by something like:
Stock.REFERENCE
JurnalTransMoves.SUF
SUM(JurnalTransMoves.SUF) Group By Stock.REFERENCE
5752
10
60
5752
20
5752
30
5753
400
3000
5753
500
5753
600
5753
700
5753
800
5754
7
15
5754
8
Is there any chance to do that?
Thanks in advance,
Aldo.
Code Snippet
SELECT
Accounts.FULLNAME AS 'ACCOUNTS.FULLNAME',
Accounts.ACCOUNTKEY AS 'ACCOUNTS.ACCOUNTKEY',
Accounts.FILTER AS 'ACCOUNTS.FILTER',
Accounts.SORTGROUP AS 'ACCOUNTS.SORTGROUP',
AccSortNames.SORTCODENAME AS 'AccSortNames.SORTCODENAME',
Accounts.CreditTermsCode AS 'Accounts.CreditTermsCode',
CreditTerms.DETAILS AS 'CreditTerms.DETAILS'
CreditTerms.CURRENF AS 'CreditTerms.CURRENF'
CreditTerms.MONTH AS 'CreditTerms.MONTH',
CreditTerms.DAYS AS 'CreditTerms.DAYS',
CreditTerms.SHAREPRC AS 'CreditTerms.SHAREPRC',
CreditTerms.TEMF AS 'CreditTerms.TEMF',
CASE
WHEN CAST(Accounts.VatExampt AS int) = 0 THEN 'x'
WHEN CAST(Accounts.VatExampt AS int) = 1 THEN 'y'
ELSE 'Undefined' END AS 'VAT',
Stock.DOCUMENTID AS 'Stock.DOCUMENTID',
DocumentsDef.DOCNAME As 'DocumentsDef.DOCNAME',
CASE
WHEN CAST(Stock.DOCUMENTID as int) = 1 THEN Stock.DOCNUMBER
WHEN CAST(Stock.DOCUMENTID as int) = 3 THEN Stock.DOCNUMBER
WHEN CAST(Stock.DOCUMENTID as int) = 35 THEN Stock.DOCNUMBER
WHEN CAST(Stock.DOCUMENTID as int) = 120 THEN Stock.DOCNUMBER
WHEN CAST(Stock.DOCUMENTID as int) = 31 THEN Stock.REFERENCE
WHEN CAST(Stock.DOCUMENTID as int) = 44 THEN Stock.REFERENCE
WHEN CAST(Stock.DOCUMENTID as int) = 34 THEN Stock.REFERENCE
WHEN CAST(Stock.DOCUMENTID as int) = 43 THEN Stock.REFERENCE
WHEN CAST(Stock.DOCUMENTID as int) = 40 THEN Stock.REFERENCE
ELSE '' END AS 'Invoice No',
Stock.VALUEDATE AS 'Stock.VALUEDATE',
JurnalTrans.DESCRIPTION AS 'JurnalTrans.DESCRIPTION',
JurnalTrans.REF2 AS 'JurnalTrans.REF2',
JurnalTransMoves.SUF AS 'JurnalTransMoves.SUF',
JurnalTransMoves_1.SUF AS 'JurnalTransMoves_1.SUF',
JurnalTransMoves.TRANSID AS 'JURNALTRANSMOVES.TRANSID'
FROM
JURNALTRANSMOVES AS JurnalTransMoves_1
INNER JOIN JURNALTRANSMOVES AS JurnalTransMoves
INNER JOIN (SELECT DISTINCT JURNALTRANSID, RECEIPTSTOCKID, FULLMATCH, TABLFNUM, CKCODE, RSORT, RUSEFID FROM RECEIPTJURNALMATCH) AS ReceiptJurnalMatch_1 ON ReceiptJurnalMatch_1.JURNALTRANSID = JurnalTransMoves.ID
INNER JOIN ACCOUNTS AS Accounts ON JurnalTransMoves.ACCOUNTKEY = Accounts.ACCOUNTKEY
INNER JOIN JURNALTRANS AS JurnalTrans ON JurnalTransMoves.TRANSID = JurnalTrans.TRANSID
INNER JOIN STOCK AS Stock ON JurnalTrans.STOCKID = Stock.ID ON JurnalTransMoves_1.TRANSID = JurnalTrans.TRANSID AND JurnalTransMoves_1.ACCOUNTKEY = Accounts.ACCOUNTKEY
LEFT OUTER JOIN ITEMS AS Items
INNER JOIN STOCKMOVES ON Items.ITEMKEY = STOCKMOVES.ITEMKEY
INNER JOIN ITEMSORTNAMES AS ItemSortNames ON Items.SORTGROUP = ItemSortNames.ITEMSORTCODE ON Stock.ID = STOCKMOVES.STOCKID
LEFT OUTER JOIN ACCSORTNAMES AS AccSortNames ON Accounts.SORTGROUP = AccSortNames.ACCSORTCODE
LEFT OUTER JOIN CREDITTERMS AS CreditTerms ON Accounts.CREDITTERMSCODE = CreditTerms.CREDITTERMSCODE
LEFT OUTER JOIN DOCUMENTSDEF AS DocumentsDef ON Stock.DOCUMENTID = DocumentsDef.DOCUMENTID
WHERE
Accounts.SORTGROUP Between '3001' And '3020'
AND Accounts.ACCOUNTKEY IN ('123456')
ORDER BY Accounts.ACCOUNTKEY
View 22 Replies
View Related
Jun 23, 2008
SELECT OCRD.CardCode AS 'Customer No.', INV1.DocEntry as 'Doc En.', INV1.Text as 'Txt'
FROM INV1, OCRD
GROUP BY OCRD.CardCode, INV1.DocEntry, INV1.Text
This is the code that I expected to execute, but I am getting an error
When i put Text in the Group By its gives me an error
When i take Text out of the Group By it gives me an error
CardCode has numbers in it..DocEntry has numbers in it..Text has NULLs in it or blank values
Is there a way I can keep the group by and making this code work?
Any help is appreciated, thank you
View 8 Replies
View Related
May 12, 2008
I'm trying to show the max date from a field by group. I've tried Max(OrderedDate) but it still shows me all the dates from the group field I only want to see the last date. I'm using report builder FE, which I can't modify the BE. Any ideas I can get this to work?
View 1 Replies
View Related
Oct 17, 2007
hi everyone,
I have a little problem concerning a SQL statement I need to do.
here is my statement:
SELECT Location, [Week of Availability], GSDC_Name, PA_Name, CA_Name, MAX([Year of Availability]) AS [Year]
FROM dbo.tbl_availability
GROUP BY Location, GSDC_Name, PA_Name, CA_Name, [Week of Availability]
now the problem in this statement is that I don't want to group by Week of Availability, but I do want to select it. BUT, if I remove Week of availability from the Group by part, I get an error! how can I do this?
View 6 Replies
View Related
Mar 23, 2006
A client wants to keep track of the number of searches for keywords ina date range. So, I'm storing each occurance of a search in a table.The columns are:PK: idsearch_stringsearch_dateI'm trying to wrap my head around how I would select the number ofoccurances for each string, divided by days. The desired result wouldlook something like:search_date search_string numOccurances----------------------------------------------------------March 20 dogs 4March 20 pigs 2March 21 dogs 8March 22 pigs 3March 22 pigeons 5I've tried a query like:selectsearch_string, CONVERT(CHAR(11),search_date,106) as search_date,count(search_string) as numOccurancesfrom searcheswhere search_date >= dateadd(d,-3,getdate())group by search_date, search_stringbut it doesn't give me the desired results. I'm sure I'm just lookingat it the wrong way.Suggestions?Thanks!
View 2 Replies
View Related
Jul 20, 2005
I need this to work:SELECT [ID] FROM [test] WITH (NOLOCK) where [a/c/d]='a' GROUP BY [unit#],[EFF DATE] HAVING COUNT ([unit #]) > 1The problem is that I get an error that [ID] needs to be in the GROUPBY clause or aggregate function. if I put it in there, I will get noduplicates (because it is the identity field). The whole point ofthis is to find dups.Thanks for any help.Robby
View 3 Replies
View Related
May 19, 2008
I am working on some sql for a report and i am getting an error with the group by statement.
SELECT Member.memberNo, Member.FamilyName, Member.GivenName, Member.gender, Membership.FeesPaid, (Membership.Fees - Membership.FeesPaid) AS OutstandingFees FROM Membership
INNER JOIN Member ON Member.memberNo = Membership.memberNo
GROUP BY Member.registrationNumber;
This fails because of the GROUP BY.
Does anyone know why this is the case?
This is the error that is produced:
SELECT DISTINCT Member.memberNo, Member.FamilyName, Member.GivenName, Member.gender, Membership.FeesPaid FROM Membership
*
ERROR at line 1:
not a GROUP BY expression
If someone could help this would be greatly appreciated.
cheers,
View 3 Replies
View Related
Sep 3, 2015
Using this IIF statement:
=CountDistinct(IIF(Fields!Released_DT.Value = Fields!Date2.Value, Fields!Name.Value,
Nothing))
Released_DT = a date - 09/03/2015 or 09/02/2015
Date2 = returns another date value in this case 09/03/2015
What I'm trying to do is: count distinct number of people (Fields!Name.Value) if the Relased_DT = Date2.My IIF statement is returning a zero value.
View 4 Replies
View Related
Apr 23, 2007
How can i group the data returned by statement below by "Dept" field. I have already tried to group it like shown below but that doesn't seem to work for me. Any help.
cmd_select = New SqlCommand("SELECT Incident_id,Dept From Report_Incident Group By Dept,Incident_id", myconnection_string)
View 3 Replies
View Related
Jul 23, 2001
Hello,
Not sure how to do this. I think I need to use a Group By query. I have a "Products" table with the following fields:
Program#
Number
UPC
Item
Item#
Size
Dept
Everything is specific to the Program#. In other words, for every instance of a Program# there can be more than one Product, which is specified by the "Number" field. So: SELECT * FROM Product WHERE [Program#] = '12345' should return this:
12345 | 1 | 000012345678 | Cookies | 98765 | 12ct | Retail |
12345 | 2 | 000012345678 | Cake | 98765 | 12ct | Retail |
12345 | 3 | 000012345678 | Ice Cream | 98765 | 12ct | Retail |
However, some recordsets are returning like this:
12345 | 1 | 000012345678 | Cookies | 98765 | 12ct | Retail
12345 | 1 | 000012345678 | Cake | 98765 | 12ct | Retail
12345 | 2 | 000012345678 | Ice Cream | 98765 | 12ct | Retail
In which case I have to fix them (the "Number" field) with an update query sp they are numbered sequentially. Luckily, this doesn't happen very often.
Can someone help me with a query that will return only those records with duplicate values in the "Number" field? I am not sure how to construct this query which I will use as a stored procedure.
TIA,
Bruce Wexler
Programmer/Analyst
View 1 Replies
View Related
May 9, 2014
I am a student, and I am so confused by SQL code that calculates incomes of my contract in a year or each month of year.
I have 3 tables:
lodgings_Contract:
id_contract indentity primary,
id_person int,
id_room varchar(4),
day_begin datetime,
day_end datetime,
day_register datetime
money_per_month money
electric:
id_electric indentity primary key,
id_room varchar(4),
number_first int,
number_last int,
Sum_Number int,
money_electric money,
status bit
Water:
id_Water indentity primary key,
id_room varchar(4),
number_first int,
number_last int,
Sum_Number int,
money_water money,
status bit
Now what I want to do are statistics on how much money I got in a year or month. Here is my code to calculate incomes of year.
Select Year(day_register) as 'Year'
, Sum(money_per_month * month(day_end-day_register)) + sum(b.money_electric+c.money_water) as 'Incomes'
From lodgings_Contract a
, electric b
, Water c
Where a.id_room = b.id_room
And a.id_room = c.id_room
And b.status = 1
And c.status = 1
Group by Year(day_register)
View 4 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
Mar 11, 2008
Hello Friends,
I am creating a report in which I want to create group column using two field value. Is it possible to do so? We have a requirement in which we are fetching data from two different hierarchy.
A B C D E F G H I J K
L 1 2 3....................4 5
M .............................
N .........................
O .......................
P
The report matrix look like the above one. The elements A,B,C are coming from one hierarchy and D,E,F,G,H,I,J,K are coming from other hierarchy. But i have created one data set to fetch the values for the report. But while creating the column group I am getting both two diff fields so I am not able to use it in single Column group and I want to use only one column group.
Can anybody help me out to solve this issue?
View 1 Replies
View Related
Oct 8, 2015
I have the following querry:
SELECT APHIST.ReturnDate AS ATDATE
,API_HIST.[ActionPlanItemID]
,API_HIST.[ActionPlanID]
,PIT.[ProductItemID]
,PIT.ProductItemCode
,PIT.Name,
[Code] ....
That query is suppose to add to calculation field OutStock and InStock based on the value of n
When executing this query I get the following message :
Column 'Sales.ActionPlan_History.ReturnDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
View 3 Replies
View Related
Apr 16, 2008
I know only enough SQL to be dangerous, but I'm stumped by this general task.
I need to know the method for selecting info from several fields of the records that satisfy a group function criteria on one of the fields. Example: Table contains Employees, products, amounts. If I group on Employee, and product, I can find the total amount of each product's sales for that employee.
create view prod_sales_by_emplyoee
select
employee
product
SUM(amount) as empl_prod_total
from sales
group by employee, product
But if I want to know which product made each employee the most money, I don't know how to do that, because the MAX function works on each field individually, not by row. From the above view, (or a table created from it) I want to know how to identify, for each employee, the product they made the most money selling, and what proportion of their total sales that product accounted for. Then, I'd like to use those results to eliminate the top tier, and find their second place product, etc.
I can do this in Access by sorting the table produced from the above view by empl_prod_total in decending order, joining that to a view that groups by employee only and provides a total, then using a grouped query on the joined data, I can use the "FIRST" function to find the values in each field of the record with the highest sales for that employee. However SQL doesn't have the FIRST function in its aggregate functions.
I suspect there's a standard way SQL does this, but it wasn't in my class. HELP!
View 7 Replies
View Related
May 7, 2006
This part is solved, please see my question at the bottom of this topic
Hi, my problem is the following, doesn't seem too hard but it's been puzzling me for hours now, and haven't solved it yet, so I need your help!
I'm creating an application to read from an existing db running on SQL-server 2000. This db contains the tables: tClient, tCommunication and tAddress. All of the three tables share a field called nClient which is the unique key in tClient, but each client can have multiple adresses and communication (phone/email etc), so several nClient can occur with the same value in tCommunication and tAddress.
I want to run a query which searches for a text (%blah%) in several fields in any of the tables, but I don't want the query to return the same client more than once, which now does happen if a client has more than one address. Help me! I want to to either return the clients with only the address that contained the %blah% or only the first address it finds for the client. Please help!
Simplified version of my query is like this:
SELECT sLastname, sStreet, sCommunication FROM tClient
LEFT JOIN tCommunication ON tClient.nClient = tCommunication.nClient
LEFT JOIN tAddress ON tClient.nClient = tAddress.nClient
WHERE tClient.sLastname LIKE '%blah%'
OR tClient.sCompanyName LIKE '%blah%'
OR tClient.sClient LIKE '%blah%'
OR tClient.sFirstname LIKE '%blah%'
OR tClient.mMemo LIKE '%blah%'
OR tCommunication.sCommunication LIKE '%blah%'
OR tAddress.sStreet LIKE '%blah%'
OR tAddress.sNumber LIKE '%blah%'
OR tAddress.sZip LIKE '%blah%'
OR tAddress.sCity LIKE '%blah%';
View 8 Replies
View Related
Jan 24, 2008
Hello everyone,
I'm trying to practice some SQL statements from my Web Data Administrator. Until now everything has worked fine. However, when I write the following statement:
SELECT DATEPART(m, Date) + '/' + DATEPART(yyyy, Date) AS Month, COUNT(*) AS NumberOfEvents FROM CompanyEvents GROUP BY DATEPART(m, Date) + '/' + DATEPART(yyyy, Date)
I get the following error " Server: Msg 245, Level 16, State 1, Line 1Conversion failed when converting the varchar value '/' to data type int."
Do I have to change something on the database?
Now, I tried to google the msg 245 and I cannot get any clear answers on the topic.
I would greatly appreciate if someone could give me some guidance on this problem.
Thanks,
Eduardo
View 2 Replies
View Related
May 21, 2008
I have the following SQL Statement:
SELECT CONVERT(char(10), FixtureDate, 101) AS Date, COUNT(*) AS 'NumberOfRecords'FROM tblFixturesGROUP BY CONVERT(char(10), FixtureDate, 101)
I want to add a new column called "need results".
This column needs to be count if a certain cell is NULL.
Count If HomeScore IS NULL
as well as grouping by date and counting the number of records. So the third column needs to count the number of records where homescore IS NULL
View 1 Replies
View Related
Apr 12, 2004
Hi,
I currently have a table whose DDL is as follows:
CREATE TABLE [tblSales] (
[OrderID] [int]
[SaleDate] [smalldatetime] ,
[ProductCode] [nvarchar] (255) ,
[QtySold] [float] ,
[UnitPrice] [float] ,
[Discount] [float] ,
[GrossSaleAmount] NULL ,
[NetSaleAmount] [float]
)
The GrossSaleAmount and NetSaleAmount are calculated fields. But for this post, kindly ignore why I am storing calcuated fields...
QUESTION:
What I want to do is to populate another table (the DDL of which is give below) from tblSales in such a manner that the TOTAL sales from each product for each available
date is grouped/summed together.
CREATE TABLE [tbl_Product_Grouped_Sales] (
[SaleDate] [smalldatetime] ,
[ProductCode] [nvarchar] (255) ,
[TotalQtySold] [float] ,
[NetSaleAmt] [float]
)
Thanks in advance for your help.
View 1 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
Apr 8, 2014
I recently had an interview and the interviewer has asked me to use a group by clause in an update statement. Here is the table and data:
Table name: Customer
cust Size Geo Segment
BP LG EU
Accenture LG US
MSC MED US
SclumbergerLG US
So the request is to update the Segment column with a unique number starting with number 1.
So for BP it should be 1, Accenture it should be 2, MSC it should be 3, Sclumberger should be 1 since the size and geo are not unique. He asked me to use a group by size and geo to generate a unique number for each combination and than make the update.
View 2 Replies
View Related
May 13, 2007
Hi guys,
I am having a little diffulty displaying two columns next to each other in a case/group by statement as code shown below.
I was wondering if i could have the results displayed such that the gst_amount and total_amount are in two separate columns (as they currently are) however the results of the rows are in the same row not in separate rows (as they are currently). I dont think i can do this in the current case statement that i have (i.e: the two case statements).
Any feedback would be appreciated
SELECT distinct
PERIOD.STARTDATE,
temp_111.EVENTTYPEID,
case when temp_111.[name] like '%GST%'
then sum(temp_111.CONTRIBUTIONVALUE)
end as GST_AMOUNT,
case when temp_111.[name] not like '%GST%'
then sum(temp_111.CONTRIBUTIONVALUE)
end as Total_Amount
FROM temp_111 INNER JOIN PERIOD
ON temp_111.PERIODSEQ = PERIOD.PERIODSEQ
WHERE
(NOT temp_111.PRODUCTID = 'IIIE' OR temp_111.PRODUCTID IS NULL)
AND temp_111.PERIODSEQ in ('111')
group by PERIOD.STARTDATE,
temp_111.EVENTTYPEID,
temp_111.[name]
Here is the current result displayed:
Startdate eventtypeid gst_amount Total_amount
2006-11-01 00:00:00.000NelNULL 83470.5608000000
2006-11-01 00:00:00.000NelNULL 161408.5264874810
2006-11-01 00:00:00.000NelNULL 677568.2683000000
2006-11-01 00:00:00.000NelNULL 2645478.1215092400
2006-11-01 00:00:00.000Nel8347.0560800000 NULL
2006-11-01 00:00:00.000Nel16140.8526488160NULL
2006-11-01 00:00:00.000Nel67756.8268300000NULL
2006-11-01 00:00:00.000Nel264547.8121507070NULL
Instead I want the result to show something like this:
Startdate eventtypeid gst_amount Total_amount
2006-11-01 00:00:00.000Nel8347.0560800000 83470.5608000000
2006-11-01 00:00:00.000Nel16140.8526488160 161408.5264874810
2006-11-01 00:00:00.000Nel67756.8268300000 677568.2683000000
2006-11-01 00:00:00.000Nel264547.8121507070 2645478.1215092400
View 2 Replies
View Related
Mar 23, 2007
Hi,I am bloody amateure and I was wondering if someone could help me editthe statement below so it groups the field SCDMASTER.SCTY_CLASS_CODEand creates one field called "total balance" equallingCUSTODY_BALANCE.OPENING_BALANCE + CUSTODY_BALANCE.DEPOSIT_AMOUNT -CUSTODY_BALANCE.WITHDRAWAL_AMOUNTSELECTCUSTODY_BALANCE.APPLICATIONCYCLEDATE,CUSTODY_BALANCE.ASSET_ID,SCDMASTER.SCTY_CLASS_CODE,CUSTODY_BALANCE.OPENING_BALANCE,CUSTODY_BALANCE.DEPOSIT_AMOUNT,CUSTODY_BALANCE.WITHDRAWAL_AMOUNTFROMFPMCAPSHIST.CUSTODY_BALANCE CUSTODY_BALANCE,CAPSREPORT.SCDMASTER SCDMASTERWHERECUSTODY_BALANCE.ASSET_ID = SCDMASTER.CUSIP_ID AND((CUSTODY_BALANCE.APPLICATIONCYCLEDATE={ts '2007-03-21 00:00:00'}) AND(SCDMASTER.SCTY_CLASS_CODE Not In('BILL','NOTE','BOND','CD','BD','CB','TINT','TPRN' ,'CA','YD')))Thanks,Andreas
View 3 Replies
View Related
Sep 2, 2007
Hi Experts, I would like to make a stored procedure in my db: I have sql express 2005.
I get Error 164 when creating this procedure:
CREATE PROCEDURE CrossTable
@Variable1 smallint,
@Variable2 smallint,
@Value smallint
AS
BEGIN
SELECT @Variable1, COUNT(@Variable1) AS 'Haufigkeiten'
FROM SurveyData
WHERE @Variable2 = @Value
GROUP BY @Variable1
END
GO
I would like to generate a frequency chart per userdefined-variable (@variable) with a where restriction.
The GROUP BY @Variable1 seems to be problem: is there some workaround in order to use variables in a GROUP BY clause? or how can I write an sql statement which do the same as this procedure (CrossTable) without using the GROUP BY clause?
Thanks a lot for your replies
Greets from Switzerland
Chris
View 3 Replies
View Related