Need Help Qith SQL Query - How To Group Sales?
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
ADVERTISEMENT
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
Sep 30, 2005
Hi
I am new to SQL and I have a unique need
I have 2 DB on the same machine DB 1 is called Test1 and DB 2 is called Test2
I want to be able to write one query to compare 2 tables from each of the Databases.
I wrote the first query ie,
use Test
SELECT Unit_Identifier,Piece_Id,Piece_Count
FROM Piece_Unit
where Version_Date = '01/01/2005' order BY Piece_Id
and I get the result from the Piece_unit Table
I wrote another query ie
use Test1
SELECT Piece_Id,PieceName,Inserts
from PalletDetails
WHERE VersionDate = '01/01/2005' ORDER BY Piece_Id
and I get the result from the PalletDetails table
The piece_id of both Tables are same value
I want to combine both queries to a single query and ONLY list the difference from both Tables in reference to the piece_id ( Only list piece_id) that exist on either table only
Thanks
View 1 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
Aug 5, 2004
Let say I have this table in MS SQL server
table transaction(date,sales)
how to query to get result like this (date,sales,sum(sales last 7 day))
I'm thinking about using self join, but it means I must have to self join 7 times to get the total sales for the last 7 day. Is there any better way to do this? or maybe special function within MS SQL server.
note: i'm not looking for total sales per week group by each week, but total last 7 day sales for each day
thanks
View 2 Replies
View Related
May 29, 2006
hello,
I need to write a query based on the top MTD sales in the series of each fabrics within series of Sales Group and Prod Group
Order by: Sales Group (alphabetical ord) , Prod Group (alphabetical ord) , sort Fabric Group based on the TOP MTD sales
Sales Gr: Active
Prod gr: Adult, Girls, Plus, LG
Fabric Gr: 1,2,3,4,5,6,7,8,...
Sales Gr: Dance
Prod gr: Adult, Girls, Plus, LG
Fabric Gr: 1,2,3,4,5,6,7,8,...
Sales Gr: Yoga
Prod gr: Adult, Girls, Plus
Fabric Gr: 1,2,3,4,5,6,7,8,...
Thank you
View 10 Replies
View Related
Aug 31, 2014
Looking for sql query for this requirement output and default get the current day and time as day 7 with count start of 7 day before. Today is Sun. Thus start day is last sun.
now the time is 22:00 & group by shop plus underline +24 hour format
Sun Mon Tue Wed Thur Fri Sat Sun Total
Shop A 20:00-21:00 $2 $10 $15 $5 $2 $10 $0 $100 $xxx
Shop A 21:00-22:00 $1 $10 $15 $5 $2 $10 $0 $100 $xxx
Shop A 22:00-23:00 $1 $10 $15 $5 $2 $10 $0 $no sales $xxx
Total $4 $20 $30 $10 $4 $20 $0 $200 $xxx
-------------------------------------------------------------------
Shop Z 22:00-23:00 $20 $15 $5 $2 $10 $0 $no sales $xxx
Shop Z 23:00-00:00 $10 $15 $5 $2 $10 $0 $no sales $xxx
Total $30 $30 $10 $4 $20 $0 $no sales $xxx
View 12 Replies
View Related
Dec 17, 2007
I'm developing a 2 year comparison rolling 12 month Sales report but am having problems with the query I'm using. Because I need to include every month (whether no sales or not) I have a calendar table named metaDates and each table gets outer joined as a result. (Forgive the long query) Please see below.
SELECT Customer.country, Order_Line_Invoice.prodcatid, MetaDates.[Month], MetaDates.[Year], isNull(SUM(Order_Line_Invoice.Sales),0) AS Sales , SUM(Order_Line_Invoice.Cost) AS Cost,
( isNull(SUM(Order_Line_Invoice.Sales),0) - isNull(SUM(Order_Line_Invoice.Cost),0) )AS GM,
'Current 12 Months' AS yearNum
FROM MetaDates
LEFT OUTER JOIN Order_Line_Invoice ON (MetaDates.Date = Order_Line_Invoice.InvoiceDate AND (Order_Line_Invoice.prodcatid IN (@GroupByFieldFilter)) )
LEFT OUTER JOIN Customer ON (Order_Line_Invoice.CustId = Customer.CustId and Customer.country IN (@country) )
LEFT OUTER JOIN Product ON (Order_Line_Invoice.ProdId = Product.ProdId)
WHERE (MetaDates.Date BETWEEN dateadd(m, datediff(m, 0, DATEADD(month,-11,@EndDate) ), 0) AND @EndDate)
GROUP BY MetaDates.[Year], MetaDates.[Month], Customer.country, Order_Line_Invoice.prodcatid
UNION
SELECT Customer.country, Order_Line_Invoice.prodcatid, MetaDates.[Month], MetaDates.[Year], isNull(SUM(Order_Line_Invoice.Sales),0) AS Sales , SUM(Order_Line_Invoice.Cost) AS Cost,
( isNull(SUM(Order_Line_Invoice.Sales),0) - isNull(SUM(Order_Line_Invoice.Cost),0) ) AS GM,
'Previous 12 Months' AS yearNum
FROM MetaDates
LEFT OUTER JOIN Order_Line_Invoice ON (MetaDates.Date = Order_Line_Invoice.InvoiceDate AND (Order_Line_Invoice.prodcatid IN (@GroupByFieldFilter)) )
LEFT OUTER JOIN Customer ON (Order_Line_Invoice.CustId = Customer.CustId and Customer.country IN (@country) )
LEFT OUTER JOIN Product ON (Order_Line_Invoice.ProdId = Product.ProdId)
WHERE (MetaDates.Date BETWEEN dateadd(m, datediff(m, 0, DATEADD(month,-23,@EndDate) ), 0) AND dateadd(m, datediff(m, 0, DATEADD(month,-11,@EndDate) ), -1))
GROUP BY MetaDates.[Year], MetaDates.[Month], Customer.country, Order_Line_Invoice.prodcatid
ORDER BY MetaDates.[Year], MetaDates.[Month]
That said the second outer join (Customer.country IN (@country)) never gets executed. It's really a simple concept. "Give me last 2 year sales by month (every previous and last month even if no sales) but only for the country the user is in"
Can someone help me out with this sql statement. Any help would be much appreciated.
View 11 Replies
View Related
Sep 21, 2015
I am trying to calculate the maximum sales per store where multiple stores exist in the same region (i.e there must be doubles of that row) in order to determine the most profitable store per that region.
E.g.
Store Sales
A 500
B 200
B 100
C 400
C 800
D 100
D 200
D 700
Looking for result set like:
B 200
C 800
D 700
View 3 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
Aug 6, 2004
Hello,
I am creating a sales report that needs to display Sales statistics for a selected month grouped by Material. Also, it needs to display the same stats for the selected month of the previous year. Finally, it needs to display Year To Date Statistics for the current year and previous year up to and including the selected month.
Currently, I am using 3 queries to do this. The first one gets the statistics the current month grouped by material. The others sprocs get their corresponding information by me passing in the material number and the month and doing the appropriate sum. So, essentially, for a single line of the report, 3 queries are being done. The problem arises in that sometimes there may be as many as 300 materils displayed in a given month, which amounts to alot of db activity and a long delay loading the report.
Is there anyway to get this information in one swoop using some fancy aggregation?
Thanks,
Jake
View 14 Replies
View Related
Jan 22, 2008
I'm trying to develop a query that provides sales data by Customer.GroupCode in monthly columns as depicted below:
GrpCd JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC TOT
Film 5,000 15,000 20,000
Aero
Elct 3,000 950 3,950
Desg
Edu 150 150
Here€™s a simplified version of the DDL:
CREATE TABLE invchead (
invoicenum int NULL ,
invoicedate datetime NULL ,
invoiceamt decimal(16, 2) NULL ,
custnum int NULL )
CREATE TABLE customer (
custnum int NULL ,
groupcode varchar (4) NULL )
The query below gets me close but it gives me gives me one row for each customer. So if I have 5 customers with the same group code, I get 5 rows for that group code. I need to modify it or come up with a different approach that gives me only one row for each GroupCode.
SELECT distinct
c.Name,
c.GroupCode,
(SELECT SUM(InvoiceAmt) FROM InvcHead WHERE InvcHead.custnum=i.custnum AND DATEPART(year, InvcHead.invoicedate)= DATEPART(year, i.invoicedate) AND DATEPART(month, InvcHead.invoicedate)=1) JAN,
(SELECT SUM(InvoiceAmt) FROM InvcHead WHERE InvcHead.custnum=i.custnum AND DATEPART(year, InvcHead.invoicedate)= DATEPART(year, i.invoicedate) AND DATEPART(month, InvcHead.invoicedate)=2) FEB,
......
(SELECT SUM(InvoiceAmt) FROM InvcHead WHERE InvcHead.custnum=i.custnum AND DATEPART(year, InvcHead.invoicedate)= DATEPART(year, i.invoicedate)) TOT
FROM InvcHead i INNER JOIN Customer c ON (i.custnum=c.custnum)
WHERE i.invoicedate>='1-1-2007' AND i.invoicedate<'1-1-2008'
Grateful for any advice that will get me closer to accomplishing this.
Thanks
View 4 Replies
View Related
Nov 28, 2012
I have a sales tables which looks as below.
DEPARTMENT
Barnd_Name
Item_Group
   S_DATE
     S_AMOUNT
Administration
IBM
[code]....
Now i need Month Wise Running Totals.but i should check the following group as show below i that order
1) DEPARTMENT
1) Brand
3) Item Group
4) Month
View 12 Replies
View Related
Apr 11, 2012
I am currently working on a website that deals with sales of products. For one of my pages for the website I need it to be able to change the current sales information for a specific product.
The top part of the following code selects the specific product however I cannot get the update query to work.
Code:
$describeQuery = "SELECT p.ID, p.NAME, dt.[Year], dt.[Month], dt.SalesVolume FROM Products p
join
(select ProductCode, sum(SalesVolume) as SalesVolume, [Year], [Month] from MonthlySales
group by ProductCode, [Year], [Month])dt
on dt.ProductCode = p.ID WHERE [NAME] = '$desiredProduct' AND [Year] = '$desiredYear' AND [Month] = '$desiredMonth'";
$editQuery = "UPDATE MonthlySales SET SalesVolume = '$NewSales' WHERE ID = '$desiredProduct' AND Year = '$desiredYear' AND Month = '$desiredMonth'";
$query = sqlsrv_query($link, $describeQuery);
View 3 Replies
View Related
Feb 4, 2015
I'm having problems filtering in my MDX query.
My requirements are to return the sales for each customer for each store, for the past 6 weeks.
The catch is that I only want those customers which have had sales over 10,000 within the last week.
This is my query:
WITH SET [CustomerList] AS
FILTER(
([Store].[Store Name].[Store Name], [Customer].[Customer Name].[Customer Name]),
[Measures].[Sales] >= 10000
AND [Date].[Fiscal Week Name].&[2015-01-26 to 2015-02-01]
[Code] ....
The dates are hard-coded for testing purposes.
View 0 Replies
View Related
Jan 10, 2014
I'd like to ask how you would get the OUTPUT below from the TABLE below:
TABLE:
id category
1 A
2 C
3 A
4 A
5 B
6 C
7 B
OUTPUT:
category count id's
A 3 1,3,4
B 2 5,7
C 2 2,6
The code would go something like:
Select category, count(*), .... as id's
from TABLE
group by category
I just need to find that .... part.
View 3 Replies
View Related
Apr 24, 2015
I need to list customers in a table that represents sales over the years.
I have tables:
Customers -> id | name |...
Orders -> id | idCustomer | date | ...
Products -> id | idOrder | unitprice | quantity | ...
I am using this SQL but it only gets one year:
SELECT customers.name , SUM(unitprice*qt) AS total
FROM Products
INNER JOIN Orders ON Orders.id = Products.idOrder
INNER JOIN Customers ON Customers.id = Orders.idCustomer
WHERE year(date)=2014
GROUP BY customers.name
ORDER BY 2 DESC
I need something like this:
customer | total sales 204 | total sales | 2015 | total sales (2014 + 2015)
--------
customer A | 1000$ | 2000$ | 3000$
customer B | 100$ | 100$ | 200$
Is it possible to retrieve these values in a single SQL query for multiple years and grand total?
View 6 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
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
Jul 26, 2007
I am back and need some help with Group By query. I am using SQL Server 2000. I have table in a database from which I have to SELECT some records. The first field is callingPartyNumber. I am trying to SELECT a few fields including callingPartyNumber, and SUM(duration). Is it possible to group the results according to the callingPartyNumber field only (knowing the fact that I have other fields also in the SELECT part of the query)?
The type of output I want is that all the records are grouped by the callingPartyNumber field and the sum of duration field for each groups is also returned.
SELECT callingPartyNumber, originalCalledPartyNumber, finalCalledPartyNumber,
dateadd(ss, (dateTimeConnect + (60 * 60 * -5))+3600 , '01-01-1970 00:00:00') AS dateTimeConnect,
dateadd(ss, (dateTimeDisconnect + (60 * 60 * -5))+3600, '01-01-1970 00:00:00') AS dateTimeDisconnect,
CONVERT(char(8), DATEADD(second, duration, '0:00:00'), 108) AS duration,
SUM(duration)
FROM Calls
GROUP BY callingPartyNumber
I cannot normalize the tables (implies that I have to work on one and only one table).Anyone...
View 25 Replies
View Related
Nov 4, 2007
I have the following sql statement below;DECLARE @val intSET @val = 1WHILE (@val <= 7 )BEGIN SELECT TOP(1) id, queueFROM itn_articles WHERE asection = @valORDER BY queue DESCSET @val = @val + 1END Essential it just loops through a select statement 7 times, now the problem is how would I do this and group my results together so I could ORDER them; instead them coming out in a different query output and, which makes the order unrankable
View 2 Replies
View Related
May 9, 2008
Hi Guys,I have the following select sql (sql server) select statement SELECT
dbo.AlternativesAssessment.AlternativeNumber,
dbo.AlternativesAssessment.UserID,
dbo.AlternativesAssessment.MembershipValue,
dbo.AlternativesAssessment.CriteriaID,
dbo.CriteriaDefinition.CriteriaName,
dbo.CriteriaDefinition.DecisionSessionID
FROM
dbo.AlternativesAssessment INNER JOIN
dbo.CriteriaDefinition ON dbo.AlternativesAssessment.CriteriaID = dbo.CriteriaDefinition.CriteriaID
WHERE (dbo.CriteriaDefinition.SessionID = @SessionID)
ORDER BY dbo.AlternativesAssessment.UserID, dbo.AlternativesAssessment.AlternativeNumber and it produces a result like this:In this case there are 4 alternatives all evaluated through 3 criteria "c1_Cost", "c2_Durability" and "c3_Reputation".My objective is to sum up all the MembershipValue's for each alternative.it shoud look like this:AlternativeNumber|CriteriaName|MembershipValue| 1 Cost 2314 1 Durability 214 1 Reputation 981 2 Cost 814 2 Durability 914 2 Reputation 381.etcDo you guys have an idea how I could achieve that?I played around with SUM's and GROUP By's for hours now but it does not seem to work.Many thanks,polynaux
View 2 Replies
View Related
Jan 11, 2001
I have a vendor table with vendor codes of six characters. The same vendor could have two codes (I'm new to this accounting system so I don't know why this is the case). If a vendor has two codes, the first character for one vendor code would be an "H", then the first character for the other code would be a "P", with the other 5 characters being the same (i.e. HCAMER and PCAMER).
We want to print only one 1099 for the vendor. So, if the vendor has two codes, I need to get a total for that vendor. Can this be done in a query?
Thanks.
View 1 Replies
View Related
Dec 18, 2006
Hi
I have a table in which column are id, date, lname, fname, add
In this table there are duplicate records but have different date for each duplicate record. I am trying to get just max(date) for each record.
when i try like these it works.
select id, max(date) from table group by id
but when i try something like these it does not work
select id, fname, lname, max(date) from table group by id, fname, lname
I get all the record.
I am writing these query from perl script. any help.
View 3 Replies
View Related
Jun 22, 2004
Hi,
Please help in writing a SQL query.
I have a table with EmpId,DirectSales,TeamLeaderId,TeamMemSales as some of the columns.The situation is
a Team Manager as sell directly, which comes under directsales and the overriding value of his team member comes under teammemsales.
Now I want get sum of both for a particular manager on a daily basis. Like
SaleDate sum(Directsales) sum(TeammemSales)
if the given id is empid then the it is directsales and if it is in teamleaderid then it is teammemsales.
How to achieve this task in query?
Any help is appreciated.
Thanks
M.L.Srinvas
View 1 Replies
View Related
Jul 23, 2004
I have a query that looks like this:
----------------------------------------------------------------------------
SELECT TOP 8 TABLEDATA.VW_DATA.COMPLETIONDATE AS COMPLETIONDATE,
TABLEDATA.VW_DATA.TYPE,
TABLEDATA.VW_DATA.TEAM_ID,
AVG(TABLEDATA.VW_DATA.DURATION) AS AVGDURATION,
TABLEDATA.VW_DATA.SITE,
TABLEDATA.MODELS.MODEL, TABLEDATA.TEAMS.DURATION,
TABLEDATA.TEAM_TYPE.TYPE AS TEAMTYPE
FROM TABLEDATA.VW_DATA
INNER JOIN
TABLEDATA.MODELS ON
TABLEDATA.VW_DATA.MODEL_ID = TABLEDATA.MODELS.MODEL_ID
INNER JOIN
TABLEDATA.TEAMS ON TABLEDATA.MODELS.MODEL_ID = TABLEDATA.TEAMS.MODEL_ID AND
TABLEDATA.VW_DATA.TEAM_ID = TABLEDATA.TEAMS.TEAM_ID
INNER JOIN
TABLEDATA.TEAM_TYPE ON TABLEDATA.TEAMS.TYPE_ID = TABLEDATA.TEAM_TYPE.TYPE_ID
WHERE (TABLEDATA.VW_DATA.DURATION > (SELECT MIN(duration) FROM TABLEDATA.VW_DATA WHERE type = 'Complete' AND team_id = 27))
AND (TABLEDATA.VW_DATA.DURATION < (SELECT MAX(duration) FROM TABLEDATA.VW_DATA WHERE type = 'Complete' AND team_id = 27))
GROUP BY TABLEDATA.VW_DATA.COMPLETIONDATE,
TABLEDATA.VW_DATA.TYPE,
TABLEDATA.VW_DATA.TEAM_ID,
TABLEDATA.VW_DATA.SITE,
TABLEDATA.MODELS.MODEL,
TABLEDATA.TEAMS.DURATION,
TABLEDATA.TEAM_TYPE.TYPE
HAVING (TABLEDATA.VW_DATA.TYPE = 'Complete')
AND (TABLEDATA.VW_DATA.TEAM_ID = 27)
AND (TABLEDATA.MODELS.MODEL <> 'Model1')
ORDER BY TABLEDATA.VW_DATA.COMPLETIONDATE DESC
----------------------------------------------------------------------------
This query results in the following:
Complete 27 88 SITEA MODELT 80 AGG 2004-05-20 00:00:00
Complete 27 83 SITEA MODELT 80 AGG 2004-05-07 00:00:00
Complete 27 70 SITEA MODELT 80 AGG 2004-05-01 00:00:00
Complete 27 110SITEA MODELT 80 AGG 2004-04-19 00:00:00
Complete 27 87 SITEA MODELT 80 AGG 2004-03-17 00:00:00
Complete 27 110SITEA MODELT 80 AGG 2004-02-27 00:00:00
Complete 27 73 SITEA MODELT 80 AGG 2004-02-26 00:00:00
Complete 27 115SITEA MODELT 80 AGG 2004-07-02 00:00:00
What I need is one row per site, and I can get that when I exclude the completiondate field, but I need to sort on that field. How can I use the GROUP BY to return only one row with the team_id, avgduration, site, model, duration, and teamtype. I do not need to display the completiondate, I just need it for sorting. The avgduration column is supposed to be an average of the duration of each of the records.
Thanks...
View 6 Replies
View Related
Oct 3, 2005
Hi all,
I have 2 tables 1 is called Phonebrands the other is called phonetypes
is it possible to show all the phonetypes gouped by the phone brand?
Cheers Wim
View 5 Replies
View Related
Oct 11, 2006
I have this table .....
AID NAME DATE AMOUNT TYPE
1001 ABC 1/1/2006 120 AC
1001 ABC 1/1/2006 23 AC
1001 BC 1/1/2006 12 AC
1001 DC 1/1/2006 22 TR
1002 ZX 1/1/2006 21 DR
1003 ABC 1/1/2006 23 AC
1003 VF 1/1/2006 44 AC
Now I want a query which will give a result set of - between a specific date
i.e between 1/1/2006- 1/2/2006
AID NAME AC_AMOUNT TR_AMOUNT DR_AMOUNT
1001 ABC 143 0 0
1001 BC 12 0 0
1001 DC 0 22 0
1002 ZX 0 0 21
----
1003 ABC 23 0 0
One row for each name,that is group up by will be on AID and NAME.
Any help will be greatly appreciated...
View 6 Replies
View Related
Jun 6, 2007
This is a fairly compliacted query and I just can't seem to figure it out.
I will try to psuedocode it to see if anyone can figure out how to do it with sql.
Table Descriptions:
I have to join two tables. Houses and Inpsections. A house can have many inspections. A single inspection can only be done on 1 house.
Houses: HouseID, HouseDescription
Inspections: InspectionID, Inspection_date, House_Id, pass_inspection ("yes"/"no"),
What I need:
I need to have only 1 result per House. For each house, I need the results for the LATEST INSPECTION. That is pass_inspection, houseID, inspection_date for the last time the house was inspected ONLY (that is if its been inspected at all - if not i still need the house listed and say "not inspected yet").
A query would be greatly appreciated.
Thanks,
DB
If you want to go further (which I will need to do) I need actually do this for each pair of House + problem. Houses and problems have a many to many relationship.
Problems: problem_id, problem_inspection
house_problems: problem_id, houseID
View 14 Replies
View Related
Jul 11, 2013
I have a table that has picktime, route_id, stlat and stlong columns.There might be multiple records having same picktime and routeid.
Select S.pickup_time, S.route_id from STARTTBL as S where Start_Dt= '7/11/2013' group by S.Route_id
(pktime) route_id stlat stlat
08:50 17 -32.01 102.98
09:50 17 -33.01 102.98
14:25 25
16:30 25
16:00 29
1.Is it possible to get the all the 4 columns where pickup_time is MAX for each Route_id
For the above data , the resulting data would be
09:50 17 -33.01 102.98
16:30 25
16:00 29
View 3 Replies
View Related
Feb 28, 2006
Can you group by a value returned by a UDF? Example:SELECT Col1, SUM(Col2), MyFunc(Col3, Col4) AS 'MyResult'GROUP BY Col1, MyResultI've had no luck. Can this be done?
View 4 Replies
View Related
Nov 14, 2006
I'm having some problem with this DMX prediction query. This is the first time I'm trying out the GROUP BY statement in the DMX query and I keep getting "Parse: the statement dialect could not be resolved due to ambiguity." message.
Is Group By supported by the DMX? What am I missing? If not supported, could I insert the result into a temporary table using SELECT ... INTO.. FROM and run a group by on a temporary table?
This is what the DMX query looks like...
SELECT
t.[AgeGroupName],
t.[ChildrenStatusName],
t.[EducationName],
Sum(t.[Profit]) as Profit
From
[Revenue Estimate DT]
PREDICTION JOIN
OPENQUERY([DM Reports DM],
'SELECT
[AgeGroupName],
[ChildrenStatusName],
[EducationName],
[Profit],
[IncomeName],
[HomeOwnerName],
[SexName],
[Country],
[ProductTypeCode],
[ProductName],
[MailCount],
[OrderAmount],
[SalesAmount],
[MailCost]
FROM
(SELECT AgeGroupName, ChildrenStatusName, EducationName, IncomeName, HomeOwnerName, MaritalStatusName, SexName, JobName, JobTypeCode,
CompanyTypeCode, Country, ProductTypeCode, ProductName, SUM(MailCount) AS MailCount, SUM(OrderAmount) AS OrderAmount, SUM(SalesAmount)
AS SalesAmount, SUM(MailCost) AS MailCost, SUM(Profit) AS Profit, MIN(RevenueEstimateID) AS ReKey
FROM [DataMining.RevenueEstimate.Predict]
GROUP BY AgeGroupName, ChildrenStatusName, EducationName, IncomeName, HomeOwnerName, MaritalStatusName, SexName, JobName, JobTypeCode,
CompanyTypeCode, Country, ProductTypeCode, ProductName, ClientID
HAVING (ClientID = 1)) as [Prediction]
') AS t
ON
[Revenue Estimate DT].[Age Group Name] = t.[AgeGroupName] AND
[Revenue Estimate DT].[Education Name] = t.[EducationName] AND
[Revenue Estimate DT].[Income Name] = t.[IncomeName] AND
[Revenue Estimate DT].[Home Owner Name] = t.[HomeOwnerName] AND
[Revenue Estimate DT].[Sex Name] = t.[SexName] AND
[Revenue Estimate DT].[Country] = t.[Country] AND
[Revenue Estimate DT].[Product Type Code] = t.[ProductTypeCode] AND
[Revenue Estimate DT].[Product Name] = t.[ProductName] AND
[Revenue Estimate DT].[Mail Count] = t.[MailCount] AND
[Revenue Estimate DT].[Order Amount] = t.[OrderAmount] AND
[Revenue Estimate DT].[Sales Amount] = t.[SalesAmount] AND
[Revenue Estimate DT].[Mail Cost] = t.[MailCost] AND
[Revenue Estimate DT].[Profit] = t.[Profit] AND
[Revenue Estimate DT].[Children Status Name] = t.[ChildrenStatusName]
GROUP BY t.[AgeGroupName],
t.[ChildrenStatusName],
t.[EducationName]
View 3 Replies
View Related
