Need Help With SQL Statement - How To Group Sales?
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
ADVERTISEMENT
Apr 13, 2004
Hi,
I currently have a table whose structure 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.
[blue]CREATE TABLE [tbl_Product_Grouped_Sales] (
[SaleDate] [smalldatetime] ,
[ProductCode] [nvarchar] (255) ,
[TotalQtySold] [float] ,
[NetSaleAmt] [float]
)
Thanks in advance for your help.
View 13 Replies
View Related
Oct 18, 2006
I have an Access 2000 database which lists customers by name, and howmuch (in dollars) they have purchased of various products. How do Iwrite a SQL statement to select customers who make up the top 60% oftotal sales dollars? I need to have a list of customers returned.The list of customers that get returned will be used to select otherdata from the same database. (I mention this in case there are multipleways of getting the customer list).Thank you for any advice.
View 2 Replies
View Related
May 19, 2015
i am trying to find a way to link an 'initial' Sale ID of a product to 'future' Sale IDs of products that will trace back to the original Sale ID.For example, if I call the original sale , 'Sale ID #123', how can i link future Sale ID's (child[ren]) and all future sales to the original Sale ID #123? Can I use a Surrogate Key or similar function?
Parent:Sale ID #123
Children:Â Sale ID # 456,
Sale ID #789,
Sale ID #.....
how I can link the original Sales ID (Parent) to Sale ID's (child[ren]) of future purchases currently existing and in the future going forward?
View 4 Replies
View Related
Dec 9, 2014
I have the below data. I need to produce a report that shows customer and total sales who had the max sales by year.
Order ID Cust ID Year Sales
O1 C1 2000 100
O2 C1 2000 150
O1 C2 2000 50
O1 C1 2001 150
O2 C3 2001 200
Report:
Cust ID Year Sales
C1 2000 250
C3 2001 200
View 1 Replies
View Related
Sep 30, 2015
I want to calculate the sum of actual sales until a date and forecast sales after a date.I am not sure what the best approach to this problem is, but I have tried my best with the following approach. Any better ways to solve this (using DAX).
I have created a parameter table that offers the last date of each month as possible choices to the user. I have tried to create a measure that sums actual sales up until this date.
SalesQuantityActual:=IF(HASONEVALUE(parLastActualMonth[Date]);CALCULATE(factSalesActual[Quantity];factSalesActual[Date]<=VALUES(parLastActualMonth[Date]));BLANK())
Unfortunately the measure above does not work.
In addition to the parameter table, I also have a normal date table.
View 2 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
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
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
Mar 20, 2007
Hi,
I am having trouble getting my query right. i am using a stored procedure to retrieve this data and bind it to my grid view.
Problem: I can't associate a field with a column that i am returning with my record set.
Details: I have query that i want to return 9 columns (UserName, Pre-Approved, Processing, Underwriting, Conditioned, Approved, Docs Out, Docs Back, Conditions Met). The username column lists all loan agents. I want the other 8 columns to list the name of the borrower (crestline..borrower.lastname) that is associate with that loan agent and that loan state. Each time a record is found where there is a valid loan agent (UserName) that meets the 'where' conditions, there will be a borrower. the 'LoanKey' field is a primary key that lets me get the borrower name from the borrower table. I dont know how to construct my query such that those borrower names get associated with their respective column header.
if the query works, my Gridview should look like this ('Name' refers to the borrower name)
UserName | Pre-Approved | Processing | UnderWriting | Conditioned | Approved | Docs Out | Docs Back | Conditions Met
Bob | | | | Name | | | |
Bob | | Name | | | | | |
Bob | | | | | | | Name |
Steve | | | Name | | | | |
Steve | | | | | | Name | |
Here is my sql call:
SELECT cfcdb..users.username, crestline..borrower.lastname,CASE WHEN crestline..loansp.LoanStatus='Pre-Approved' THEN crestline..loansp.LoanStatus ELSE NULL END AS 'Pre-Approved',CASE WHEN crestline..loansp.LoanStatus='Processing' THEN crestline..loansp.LoanStatus ELSE NULL END AS 'Processing',CASE WHEN crestline..loansp.LoanStatus='Underwriting' THEN crestline..loansp.LoanStatus ELSE NULL END AS 'Underwriting',CASE WHEN crestline..loansp.LoanStatus='Conditioned' THEN crestline..loansp.LoanStatus ELSE NULL END AS 'Conditioned',CASE WHEN crestline..loansp.LoanStatus='Approved' THEN crestline..loansp.LoanStatus ELSE NULL END AS 'Approved',CASE WHEN crestline..loansp.LoanStatus='Docs Out' THEN crestline..loansp.LoanStatus ELSE NULL END AS 'Docs Out',CASE WHEN crestline..loansp.LoanStatus='Docs Back' THEN crestline..loansp.LoanStatus ELSE NULL END AS 'Docs Back',CASE WHEN crestline..loansp.LoanStatus='Conditions Met' THEN crestline..loansp.LoanStatus ELSE NULL END AS 'Conditions Met'FROM cfcdb..usersinner join (crestline..loansp inner join crestline..borrower on crestline..loansp.loankey = crestline..borrower.loankey)on crestline..loansp.fstnamelo=cfcdb..users.firstname AND crestline..loansp.lstnamelo=cfcdb..users.lastnameinner join cfcdb..users_roleson cfcdb..users.username = cfcdb..users_roles.usernamewhere cfcdb..users.active = 1 AND cfcdb..users_roles.groupid = 'agent'AND crestline..loansp.enloanstat <> 'Closed' AND crestline..loansp.enloanstat <> 'Cancelled' AND crestline..loansp.enloanstat <> 'Declined' AND crestline..loansp.enloanstat <> 'On Hold'order by cfcdb..users.username asc
View 2 Replies
View Related
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 Cost1 Apple 0.452 Apple 0.633 Apple 0.524 Pear 0.895 Pear 0.83
And run the query:
select max(Cost),FruitName From Fruitsgroup by FruitName
It'll correctly return:
FruitName CostApple 0.63Pear 0.89
Now i need the ID also returned by my query so i go:
select max(Cost),FruitName,ID From Fruitsgroup by FruitName,ID
This doesnt return the above results with the ID appended to it, it instead returns:
ID FruitName Cost1 Apple 0.452 Apple 0.633 Apple 0.524 Pear 0.895 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 Cost2 Apple 0.634 Pear 0.89
Thanks.
View 9 Replies
View Related
Jul 23, 2006
Hi,
Suppose a table [Inventory]:
Item Color Quantity
-------------------- -------------------- --------------------------
Table Blue 10
Table Red 20
Table Yellow 30
Chair Blue 40
Chair Red 50
I'm wondering if there is a group state like this:
Select Item, ?Function(Color), Sum(Quantity) From Inventory Group by Item
which returns this:
Table Blue,Red,Yellow 60
Chair Blue,Red 90
Does anyone has an idea how this can be achieved?
Regards,
Manolis Perrakis
View 10 Replies
View Related
Jul 20, 2005
I've always been mistified why you can't use a column alias in the group byclause (i.e. you have to re-iterate the entire expression in the group byclause after having already done it once in the select statement). I'mmostly a SQL hobbiest, so it's possible that I am not doing this in the mostefficient manner. Anyone care to comment on this with relation to thefollowing example (is there a way to acheive this without re-stating theentire CASE statement again in the Group By clause?):Select 'Age' =CaseWHEN(SubmittedOn >= DATEADD(dd, - 30, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 0, GETDATE()))THEN '0-30 Days Old'WHEN(SubmittedOn >= DATEADD(dd, - 60, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 31, GETDATE())) Then '31-60 Days Old'WHEN(SubmittedOn >= DATEADD(dd, - 90, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 61, GETDATE())) Then '61-90 Days Old'WHEN(SubmittedOn <= DATEADD(dd, - 91, GETDATE())) THEN '91+ Days Old'ELSE cast(SubmittedOn as varchar(22))end,max(SubmittedOn), COUNT(SCRID) AS NbrSCRsFrom SCRViewWHERE(StatusSort < 90) ANDCustomerID = 8 andUserID = 133group byCaseWHEN(SubmittedOn >= DATEADD(dd, - 30, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 0, GETDATE()))THEN '0-30 Days Old'WHEN(SubmittedOn >= DATEADD(dd, - 60, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 31, GETDATE())) Then '31-60 Days Old'WHEN(SubmittedOn >= DATEADD(dd, - 90, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 61, GETDATE())) Then '61-90 Days Old'WHEN(SubmittedOn <= DATEADD(dd, - 91, GETDATE())) THEN '91+ Days Old'ELSE cast(SubmittedOn as varchar(22))endOrder by max(submittedon) descThanks,Chad
View 4 Replies
View Related
Sep 25, 2015
i have this schema :
CREATE DATABASE ANDEB
USE ANDEB
CREATE TABLE TDocHeader
(
CustName VARCHAR(50) NOT NULL,
DocNum INT NOT NULL,
col1 varchar(50),
[code]...
How i can have a group by for last docnum for Customer and all columns?
View 6 Replies
View Related
Oct 16, 2006
I am currently straching my head as to why the following doesn't work as I expect.
I have Three tables UsersManagers, Users and Bookings:-
SELECT MAX(UsersManagers.UsernameUser) AS UserID, SUM(Bookings.HoursTotal) AS NumHours
FROM UsersManagers
LEFT OUTER JOIN Users ON Users.Username = UsersManagers.UsernameUser
LEFT OUTER JOIN Bookings ON Bookings.Username = Users.Username
WHERE UsersManagers.UsernameManager = 'testPM1'
AND (Bookings.DateOfBooking BETWEEN '01 October 2006' AND '31 October 2006')
GROUP BY UsersManagers.UsernameUser
This statement returns only one username with the hours they have booked in my Bookings table. However the left outer joins (I would have thought) should return an entry even if there are no hours booked for a user. If I omit the Date Section:
SELECT MAX(UsersManagers.UsernameUser) AS UserID, SUM(Bookings.HoursTotal) AS NumHours
FROM UsersManagers
LEFT OUTER JOIN Users ON Users.Username = UsersManagers.UsernameUser
LEFT OUTER JOIN Bookings ON Bookings.Username = Users.Username
WHERE UsersManagers.UsernameManager = 'testPM1'
GROUP BY UsersManagers.UsernameUser
I get two records returned
testUsr1 37.5
testUsr2 NULL
Why don't I get this when I use a date?
Thanks
View 5 Replies
View Related
Nov 26, 2007
I have a report which totals sales by customer. Then table footer has a grand total of all customer sales. I would like to get a percent of each customer's sales against the total sales. How do I get the sum from the table footer to use in an individual customer row?
Thanks.
View 3 Replies
View Related
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
Jan 20, 2015
I am trying to get a count by product, month, year even if there are is no record for that particular month.
Current outcome:
Product Month Year Count
XYZ January 2014 20
XYZ February 2014 14
XYZ April 2014 34
...
Desired outcome:
Product Month Year Count
XYZ January 2014 20
XYZ February 2014 14
XYZ March 2014 0
XYZ April 2014 34
...
The join statement is simple:
Select Product, Month, Year, Count(*) As Count
From dbo.Products
Group By Product, Month, Year
I have also tried the following code and left joining it with my main query but the product is left out as is seen:
DECLARE @Start DATETIME, @End DATETIME;
SELECT @StartDate = '20140101', @EndDate = '20141231';
WITH dt(dt) AS
(
SELECT DATEADD(MONTH, n, DATEADD(MONTH, DATEDIFF(MONTH, 0, @Start), 0))
FROM ( SELECT TOP (DATEDIFF(MONTH, @Start, @End) + 1)
n = ROW_NUMBER() OVER (ORDER BY [object_id]) - 1
FROM sys.all_objects ORDER BY [object_id] ) AS n
)
2nd attempt:
Product Month Year Count
XYZ January 2014 20
XYZ February 2014 14
NULL March 2014 0
XYZ April 2014 34
...
What I want is this (as is shown above). Is this possible?
Desired outcome:
Product Month Year Count
XYZ January 2014 20
XYZ February 2014 14
XYZ March 2014 0
XYZ April 2014 34
...
View 7 Replies
View Related
Jun 17, 2007
The following is a simplified version of my SQL statement. I am attempting to do a simple count(*) with two groupings and a where clause. This is Select command for a GridView. However, I am unable to display zeros. The rows are completely missing and I would greatly appreciate someone's help. I have already tried Group By All, but that, unfortunately, does not work. Here is my SQL statement:
"SELECT [GENDER], [RACE], COUNT(*) FROM [TABLE] WHERE ([COLUMNNAME] ='SOMETHING') GROUP BY [GENDER], [RACE]"
Thanks for the help in advance!
View 1 Replies
View Related
Dec 5, 2007
I have a query that brings back the data below. I need to divide the BudgetTotal by the Count. Then I need to go to the records that make up those €œgroups€? and enter a Budget value = BudgetTotal/Count.
How could I write this in a stored procedure or a SQL statement if possible?
Thanks.
Kevin
SELECT TOP 100 PERCENT dbo.ReportTable.ProjectNo, dbo.ReportTable.Category, dbo.ReportTable.Type, COUNT(dbo.ReportTable.ProjectNo) AS count,
dbo.ReportTable.Budget, dbo.OracleDownloadBudget.Budget AS Expr1
FROM dbo.ReportTable INNER JOIN
dbo.OracleDownloadBudget ON dbo.ReportTable.Category = dbo.OracleDownloadBudget.Category AND
dbo.ReportTable.ProjectNo = dbo.OracleDownloadBudget.Project AND dbo.ReportTable.Type = dbo.OracleDownloadBudget.Type
GROUP BY dbo.ReportTable.ProjectNo, dbo.ReportTable.ProjectName, dbo.ReportTable.Category, dbo.ReportTable.Type, dbo.ReportTable.Budget, dbo.OracleDownloadBudget.Budget
HAVING (dbo.ReportTable.Budget < 1)
ORDER BY dbo.ReportTable.ProjectNo
ProjectNo
Category
Type
Count
Budget
BudgetTotal
100143
Travel
Travel, Meals, No Report IRS
2
0
300.27
100146
Travel
Travel Costs, Training (all)
1
0
300.27
100164
Supplies & Materials
Supplies, Educational
1
0
300.27
100167
Equipment
Eq NonCapital Desktop Comp
1
0
300.27
100170
Faculty Salaries
FB, Faculty
11
0
300.27
100170
Faculty Salaries
Salary, Faculty, T&R FT
11
0
300.27
100170
Wages
Wages, Student
2
0
300.27
100171
Faculty Salaries
FB, Faculty
19
0
300.27
100171
Faculty Salaries
Salary, Faculty, T&R FT
19
0
300.27
100176
Scholarships & Fellowships
Fell, Assist, Out, Grad
1
0
300.27
100177
Scholarships & Fellowships
Fell, Assist, In, Grad
1
0
300.27
View 5 Replies
View Related
May 18, 2006
Dear All,
I have the following set of data, where FLD1, FLD2...FLD4 are the fields.
I want to be able to select for each group FLD1,FLD2,FLD3 ORDER BY FLD4 ASC the top 2 entries
Example :
FLD1 FLD2 FLD3 FLD4
ABC XXX 1 98
ABC XXX 1 81
ABC XXX 1 5
ABC XXX 2 1
ABC XXX 2 9
ABC XXX 2 2
BBC XXX 2 2
The result I want is :
ABC XXX 1 5
ABC XXX 1 81
ABC XXX 2 1
ABC XXX 2 2
BBC XXX 2 2
Is there a way to do it using SQL?
Thanks in advance
View 4 Replies
View Related
Sep 22, 2014
I am trying to use SQL to pull unique records from a large table. The table consists of people with in and out dates. Some people have duplicate entries with the same IN and OUT dates, others have duplicate IN dates but sometimes are missing an OUT date, and some don’t have an IN date but have an OUT date.
What I need to do is pull a report of all Unique Names with Unique IN and OUT dates (and not pull duplicate IN and OUT dates based on the Name).
I have tried 2 statements:
#1:
SELECT DISTINCT tblTable1.Name, tblTable1.INDate
FROM tblTable1
WHERE (((tblTable1.Priority)="high") AND ((tblTable1.ReportDate)>#12/27/2013#))
GROUP BY tblTable1.Name, tblTable1.INDate
ORDER BY tblTable1.Name;
#2:
SELECT DISTINCT tblTable1.Name, tblTable1.INDate
FROM tblTable1
WHERE (((tblTable1.Priority)="high") AND ((tblTable1.ReportDate)>#12/27/2013#))
UNION SELECT DISTINCT tblTable1.Name, tblTable1.INDate
FROM tblTable1
WHERE (((tblTable1.Priority)="high") AND ((tblTable1.ReportDate)>#12/27/2013#));
Both of these work great… until I the OUT date. Once it starts to pull the outdate, it also pulls all those who have a duplicate IN date but the OUT date is missing.
Example:
NameINOUT
John Smith1/1/20141/2/2014
John Smith1/1/2014(blank)
I am very new to SQL and I am pretty sure I am missing something very simple… Is there a statement that can filter to ensure no duplicates appear on the query?
View 1 Replies
View Related
Sep 18, 2014
I've 2 tables QuestionAnswers and ConditionalQuestions and fetching data from them using CTE join and I'm seeing repetitive rows (not duplicate) like, If you have multiple answers for 1 question, the output is like
where london
where paris
where toronto
why us
why japan
why indonesia
I want to eliminate the repetitive question and group them as parent child items.
with cte as (
select cq.ConditionalQuestionID from ConditionalQuestions cq
inner join QuestionAnswers qa on cq.QuestionID=qa.QuestionID where cq.QuestionID=5 and qa.IsConditional='Y')
select distinct q.Question, a.Answer from QuestionAnswers qa
inner join Answers a on a.AnswerID = qa.AnswerID
inner join Questions q on q.QuestionID = qa.QuestionID
inner join cte c on c.ConditionalQuestionID = qa.QuestionID;
View 4 Replies
View Related
Aug 28, 2015
I have a a Group By query which is working fine aggregating records by city. Â Now I have a requirement to focus on one city and then group the other cities to 'Other'. Â Here is the query which works:
Select [City]= CASE WHEN [City] = 'St. Louis' THEN 'St. Louis' ELSE 'Other Missouri City' END, SUM([Cars]) AS 'Total Cars'Â
From [Output-MarketAnalysis]
Where [City] IN ('St. Louis','Kansas City','Columbia', 'Jefferson City','Joplin') AND [Status] = 'Active'
Group by [City]
Here is the result:
St. Louis 1000
Kansas City 800
Columbia 700
Jefferson City 650
Joplin 300
When I add this Case When statement to roll up the city information it changes the name of the city to 'Other Missouri City' however it does not aggregate all Cities with the value 'Other Missouri City':
Select [City]= CASE WHEN [City] = 'St. Louis' THEN 'St. Louis' ELSE 'Other Missouri City' END, SUM([Cars]) AS 'Total Cars'Â
From [Output-MarketAnalysis]
Where [City] IN ('St. Louis','Kansas City','Columbia', 'Jefferson City','Joplin') AND [Status] = 'Active'
Group by [City]
Here is the result:
St. Louis 1000
Other Missouri City 800
Other Missouri City 700
Other Missouri City 650
Other Missouri City 300
What I would like to see is a result like:
St. Louis 1000
Other Missouri City 2450
View 5 Replies
View Related
Sep 29, 2015
I have an SSRS 2012 table report with groups; each group is broken ie. one group for one page, and there are multiple groups in multiple pages.
'GroupName' column has multiple values - X,Y,Z,......
I need to group 'GroupName' with X,Y,Z,..... ie value X in page 1,value Y in page 2, value Z in page 3...
Now, I need to display another column (ABC) in this table report (outside the group column 'GroupName'); this outside column itself is another column header (not a group header) in the table (report) and it derives its name partly from the 'GroupName' Â values:
Example:
Value X for GroupName in page 1 will mean, in page 1, column Name of ABC column must be ABC-X Value Y for GroupName in page 2 will mean, in page 2, column Name of ABC column must be ABC-Y Value Z for GroupName in page 3 will mean, in page 3, column Name of
ABC column must be ABC-Z
ie the column name of ABC (Clm ABC) Â must be dynamic as per the GroupName values (X,Y,Z....)
Page1:
GroupName          Clm ABC-X
X
Page2:
GroupName          Clm ABC-Y
Y
Page3:
GroupName          Clm ABC-Z
Z
I have been able to use First(ReportItems!GroupName.Value) in the Page Header to get GroupNames displayed in each page; I get X in page 1, Y in page 2, Z in page 3.....
However, when I use ReportItems (that refers to a group name) in the Report Body outside the group,
I get the following error:
Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope
I need to get the X, Y, Z ... in each page for the column ABC.
I have been able to use this - First(Fields!GroupName.Value); however, I get ABC-X, ABC-X, ABC-X in each of the pages for the ABC column, instead of ABC-X in page 1, ABC-Y in page 2, ABC-Z in page 3, ...
View 4 Replies
View Related
