Groupby

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


ADVERTISEMENT

Max With Groupby

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

Use Of Groupby And Get Max Val

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

Using MAX And GROUPBY

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

Groupby Unions

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

Help Debug Groupby

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

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 View Related

Sum Of Groupby Query

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

How To Display Records Only Once Using Groupby In

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

Subquery Headache With Count And GroupBy

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

Delete Records Not Matching Top25 In GroupBy

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

Reverse GroupBy Kind Of Flat File...

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

T-SQL (SS2K8) :: How To Add Manual Column Based On GroupBy For Title

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







Copyrights 2005-15 www.BigResource.com, All rights reserved