Question About Sum, GroupBy, Having..
Aug 9, 2006
Fred writes "How to retrieve rows for which sum of the qty field equals to a specified number - let's consider a basic table as with 3 fields : item (pk), qty, orderid (fk) :
item qty orderid
**** *** *******
itemA 1 ord-18/07/06
itemB 2 ord-01/01/05
itemC 2 ord-18/07/06
itemD 3 ord-18/07/06
itemE 1 ord-18/07/06
I want to know belong ord-18/07/06's items, which ones to pick up in order to reach a total qty of 5. Answer is itemC (2) + itemD (3). I tried with various combinations of SUM,MAX,HAVING.. Can't make it works. Help appreciated. Rgds."
View 1 Replies
ADVERTISEMENT
Apr 8, 2008
i have one query:
SELECT MAX(salesid) AS salesid, max(salestitle) as salestitle
FROM saleshistory s where list = 'sales'
GROUP BY DATEADD(dd, DATEDIFF(dd, 0, salesdate), 0)
order by salestitle
i m getting more than 1000 rows.. in output
some of them are :
salesid salestitle
---------------------------------
11871/20/2005 9:45:54 AM
821622K8_KTBcalendar_pre1 9/19/2007 10:46:02 AM
824962K8_KTBcalendar_pre1 9/20/2007 3:29:44 PM
918572K8_KTBcalender_inst 10/16/2007 2:47:31 PM
175660AmerHeartMo_2008 2/22/2008 1:05:24 PM
179614AmerHeartMo_2008 2/26/2008 4:54:18 PM
2554American_Heart_Month_Feb2005
25335Cholesterol_Educ_Mo_2006_Rem_test
25186Cholesterol_Educ_Mo_2006_Rem_test
25282Cholesterol_Educ_Mo_2006_Rem_test
i want (desired)output like: -
11871/20/2005 9:45:54 AM
824962K8_KTBcalendar_pre1 9/20/2007 3:29:44 PM
918572K8_KTBcalender_inst 10/16/2007 2:47:31 PM
179614AmerHeartMo_2008 2/26/2008 4:54:18 PM
2554American_Heart_Month_Feb2005
25335Cholesterol_Educ_Mo_2006_Rem_test
i want max(salesid) among that same salestitle
when i m doing like:
SELECT MAX(salesid) AS salesid, max(salestitle) as salestitle
FROM saleshistory s where list = 'sales'
GROUP BY substring([salestitle],1,charindex(' ',[salestitle])-1)
i m getting an error
Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.
(0 row(s) affected)
can anybody help me to get correct output.
thanks.
View 9 Replies
View Related
Feb 10, 2007
Respected Friends,
kindly solve my query.
Table Structrue:
Table Name : Custom_Table_SPPR_Master
Fields :
Sno int pk
SPPR_No int pk
Revison_No int pk
rev_Date date
prop_amt decimal
claim_amt decimal
avail_amt decimal
Status int
Data
Sno SPPR_No Revison_No rev_Date prop_amtclaim_amtavail_amtStatus
1 2000 0 2006-10-01 00:00:00.000 1000 600 400 2
2 2000 1 2006-10-01 00:00:00.000 1100 600 500 2
3 2000 2 2006-10-01 00:00:00.000 1400 600 800 2
4 2000 3 2006-10-01 00:00:00.000 1500 900 600 2
5 2001 0 2006-10-01 00:00:00.000 1000 200 800 2
6 2001 1 2006-10-01 00:00:00.000 1200 600 600 2
7 2002 0 2006-10-01 00:00:00.000 1000 600 400 2
8 2002 1 2006-10-01 00:00:00.000 1100 600 500 2
9 2003 0 2006-10-01 00:00:00.000 1000 600 400 2
I want the record where max of the revision no is seen for each sppr
that is the result should the rows of sno: 4,6,8,9
i tried the following but in vein
select max(revision_no) ,sppr_no from Custom_Table_SPPR_Master group by sppr_no
-- in this i am getting records, but i want * datas
select distinct(sppr_no) from Custom_Table_SPPR_Master where revision_no in(select max(revision_no) from Custom_Table_SPPR_Master group by revision_no) group by sppr_no
select sppr_no,count(revision_no) from Custom_Table_SPPR_Master where revision_no in(select max(revision_no) from Custom_Table_SPPR_Master group by revision_no)
group by sppr_no,revision_no
Kindly let me the answers . thanks in advance.
View 2 Replies
View Related
Apr 23, 2007
Hi,
I need help in creating a query using MAX and group by.
The input SQL table ClientInfo contains data like the following:
Client Div MaxDt
00003 00 19820501
00003 00 19830208
00003 00 19850801
00003 00 20040811
00003 50 19850101
00003 50 20040811
00003 99 19870131
I need the output to have the max date for every division of every client and output should be as follows:
Client Div MaxDt
00003 00 20040811
00003 50 20040811
00003 99 19870131
Thanks,
View 7 Replies
View Related
Mar 17, 2008
Hi.I've wrote this query:
SELECT TOP 10 COUNT(*) AS ItemsSold, ProductName, order_details_product_id, SUM(order_details_price) AS order_details_price
FROM tbl_Detail_Order_Product
WHERE (OrderStatus NOT IN (14, 15))
GROUP BY order_details_product_id, ProductName, order_details_price
ORDER BY ItemsSold DESC
which works fine and have these results:
Purchased Returned Sold Part Number
$ 598.29 37 16684
$ 200.29 33 16654
$ 300.29 25 16184
.
.
.
my problem is I want to show the returned column wich is:
WHERE (OrderStatus IN (14, 15))
I dont know how can I do that.
I tried to write another query but it didnt worked correctly.
plz help me.
thanks
View 1 Replies
View Related
May 6, 2004
hi
I have MSSQL query that performs multiple UNION ,but I would like to perform a GROUPBY on the whole result set.
How Can i do this?
plz help...
bono
View 4 Replies
View Related
Jun 15, 2008
alter PROCEDURE [dbo].[spsite]
@fromdate datetime,
@todate datetime
AS
BEGIN
select site as sitecode,
count(id) as t from an where mydate >=@fromdate and mydate<=@todate,
count(id) as p from an where p=1 and mydatestage1 >=@fromdate and mydatestage1<=@todate
group by site
what am i doing wrong?
I get incorrect syntax near ,
View 7 Replies
View Related
Feb 19, 2008
Hi,
I have a problem regarding this i have this statement where in i can count the number of data and display what category is that.... my problem is how can i sum itHere's the Statement
SELECT Count([PullOut].[SOType]) as Count, [SODescription].[SODescription]
FROM PullOut INNER JOIN SODescription ON [PullOut].[SOType]=[SODescription].[SOType]
WHERE PullOut.Area in ('Rizal','Cavite')
GROUP BY [SODescription].[SODescription];
Output:
1 Category1
5 Category2
2 Category3
....i want only to display the sum of this....
8 Total
Regards
View 5 Replies
View Related
Mar 31, 2008
in my sql query,when it run then it displays records as follows
dt_id daily_type dates emp_mgmt_id emp_name etc--------
48 Electrical 03/14/2008 22 abc
----------
49 Mechanical 03/14/2008 35 xyz
-------------
48 Electrical 03/14/2008 22 abc
----------
49 Mechanical 03/14/2008 35 xyz
-------------
i want to display records only once like as follows
dt_id daily_type dates emp_mgmt_id emp_name etc--------
48 Electrical 03/14/2008 22 abc
----------
49 Mechanical 03/14/2008 35 xyz
-------------
if i use group by on some fields then it gives error as follows
Column 'Daily_Time_Entry.daily_type' is invalid in the select list because it is
not contained in either an aggregate function or the GROUP BY clause.
so how can i design query so records display only once not repeated,or is any
alternative,to solve this .
my sql query as follows
SELECT dl.dt_id, dl.daily_type, convert(char,dl.dt_date,101) as
dates,dl.emp_mgmt_id,
emp.emp_name,emp.employee_id,dl.project_type,dl.project_id,
dl.cec_job_id,
cec.cecjobname,dl.time_st,dl.time_ot,dl.time_dt,op.other_proj_id,
op.customer_name,op.project_name,op.owner_rep_phone_num1,dl.work_desc,
m.material_id,m.material_type,m.material_date, m.project_type
,m.project_id,
m.qty,m.description,m.material_unit_price,m.material_markup,
m.material_subtotal,
m.cec_job_id,m.location
FROM material m left outer join
Other_Project op on m.project_id=op.other_proj_id left outer join
Daily_Time_Entry dl on
dl.project_id =op.other_proj_id inner join Employee_Mgmt emp
on emp.emp_mgmt_id = dl.emp_mgmt_id inner join CEC_Job cec
ON dl.cec_job_id = cec.cec_job_id
where (dl.dt_date='3/14/2008'and m.material_date='3/14/2008') and
(dl.project_type='Other Project' and m.project_type = 'Other Project')
and (dl.cec_job_id=m.cec_job_id) and (dl.daily_type='Electrical') and
(dl.project_id='18')
uday
View 3 Replies
View Related
Nov 23, 2005
I'm trying to return an integer from the following table that returnsthe number of unique cities:tblEmployeesName CityJohn BostonFrank New YorkJim OmahaBetty New YorkThe answer should be 3.DECLARE @EmployeeCities intSELECT @EmployeeCities = SELECT ... ???How go I return one row/column into @EmployeeCities from a Count and aGroupBy?Headache already... Maybe it's too early...
View 3 Replies
View Related
Jul 23, 2005
I have a table (tblA) that records the RecordID, UserID andLastViewedDate (DateTime) of each record opened in tblB where RecordIDis the PK in tblB. I want to construct a query that groups all recordsin tblA by RecordID, filters by UserID and keeps only the most recent25 RecordIDs and deletes the rest.This gets me a recordset of all RecordIDs filtered by UserID in tblAbut I can't figure out how to sort it by LastViewedDate DESC and toeliminate those not in the Top25:SELECT RecordIDFROM dbo.tblAWHERE (UserID = 1234)GROUP BY RecordIDAny help is appreciated!lq
View 4 Replies
View Related
Nov 16, 2007
Hi!
I have a data (txt) file which has the following structure
M_ID1:
CID11,Date11
CID12,Date12
M_ID2:
CID,Date21
CID,Date22
My problem now is it, to get that into a table (MID,CID,DATE) as the text file "groups" CID and DATE by MID and
I have no idea how to read the file and transform it.
Well, perhaps somebody out there has an idea!
Thanks in advance!
alx
View 4 Replies
View Related
Jan 15, 2015
I have a query to get data group by High, Medium and Low so only three rows will be returned.
select
sum(case .... ) then 1 else 0 end) as [Column1],
sum(case .... ) then 1 else 0 end) as [Column2],
sum(case .... ) then 1 else 0 end) as [Column3]
from sometable
Group by High, Medium, Low
I want to manually add High, Medium and Low to each row's first column. the final result should look like:
Column1 Column2 Column3
High 123 123 123
Medium 123 123 123
Low 123 123 123
How can I do it?
View 2 Replies
View Related
