Select all columns values using Group by

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select all columns values using Group by

Author

Topic

Kotti
Posting Yak Master

129 Posts

Posted - 2008-12-17 : 04:53:37

Select all Columns values using Group by but does not mention selected columns in Group by.

Table Name: OrdersReady

PrtygenId Varchar,CreationDateTime DateTime,UserName Varchar,Amount float,Status Varchar.

I need all columns but using one column in Group by clause.

i am using following quary

select PrtygenId, sum(Amount) as Total from Jew_ordersReady Group By PrtygenId.

But I am select All columns and one column using in group by clause.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-17 : 04:59:14

SELECT PrtygenId, CreationDateTime, UserName, Amount, Status, SUM(Amount) OVER (PARTITION BY PrtygenId)
FROM OrdersReady

E 12°55'05.63"
N 56°04'39.26"

Kotti
Posting Yak Master

129 Posts

Posted - 2008-12-17 : 05:08:25

I am Select Distinct PrtyGenId

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-17 : 05:26:13

did you meant this?


SELECT PrtygenId, CreationDateTime, UserName, Amount, Status, TotalAmt
FROM
(
SELECT ROW_NUMBER() OVER (PARTIYION BY PrtygenId ORDER BY CreationDateTime DESC) AS Seq,PrtygenId, CreationDateTime, UserName, Amount, Status, SUM(Amount) OVER (PARTITION BY PrtygenId) AS TotalAmt
FROM OrdersReady
)t
WHERE t.Seq=1

if no,post some sample data & output to illustrate what you want

kote_alex
Posting Yak Master

112 Posts

Posted - 2008-12-17 : 06:16:31

Or another way ...

select o.PrtygenId, CreationDateTime, UserName, Amount, Status, a.amount
FROM OrdersReady O
left outer join
(select prtygenid,sum(amount) Amount from ordersready) a on o.prtygenid=a.prtygenid

Kotti
Posting Yak Master

129 Posts

Posted - 2008-12-18 : 00:05:04

In SQLServer Table Jew_OrdersReady Contains Following Columns and Corresponding Values
PrtyGenId Varchar,CustomerName Varchar,Total float,PurchaseDate Datetime,OrderStatus Varchar

PrtyGenId CustomerName Total PurchaseDate Orderstatus
JEW101 Kotti 1000 12/10/2008 Success
JEW101 Kotti 5000 12/10/2008 Success
JEW101 Kotti 2000 12/10/2008 Success
JEW102 Karthik 2000 12/12/2008 UnSuccess
JEW102 Karthik 1000 12/13/2008 UnSuccess
JEW103 Karthik 2000 12/13/2008 Success
JEW103 karthik 4000 12/13/2008 Success

I need follwing Values

PrtyGenId CustomerName TotalAmount PurchaseDate OrderStatus
JEW101 Kotti 8000 12/10/2008 Success
JEW102 karthik 3000 13/11/2008 UnSuccess
JEW103 Karthik 6000 14/11/2008 Success

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-18 : 00:10:28

[code]
SELECT PrtyGenId, CustomerName, TotalAmount,PurchaseDate, Orderstatus
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY PrtyGenId, CustomerName ORDER BY PurchaseDate DESC) AS Seq,PrtyGenId, CustomerName,PurchaseDate, Orderstatus,SUM(Total) OVER (PARTITION BY PrtyGenId, CustomerName) AS TotalAmount
FROM Table
)t
WHERE seq=1
[/code]

i still cant understand from where you got last date value though (14/11/2008)

Kotti
Posting Yak Master

129 Posts

Posted - 2008-12-18 : 00:37:07

Thank you very much visakh16.It's working.I am doing mistake for purchase Date.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-18 : 01:05:48

ok...i guessed..no problem
you're welcome

Kotti
Posting Yak Master

129 Posts

Posted - 2008-12-22 : 06:35:14

In SQLServer Table Jew_OrdersReady Contains Following Columns and Corresponding Values

PrtyGenId Varchar,CustomerId bigInt,CustomerName Varchar,Total float,PurchaseDate Datetime,OrderStatus Varchar,Deleted bool,OrderStatus bool

Hi I need to select rows for particular CustomerId.

PrtyId CustName CustId Total PurchaseDate Order Deleted Process
JEW101 Kotti 1 1000 12/10/2008 Success False True
JEW101 Kotti 1 5000 12/10/2008 Success False True
JEW101 Kotti 1 2000 12/10/2008 UnSuccess true False
JEW102 Karthik 2 2000 12/12/2008 UnSuccess False True
JEW102 Karthik 2 1000 12/13/2008 UnSuccess False True
JEW103 Karthik 2 2000 12/13/2008 Success True False
JEW103 karthik 2 4000 12/13/2008 Success False True

I am using Following Where Condition
Where Deleted=0 and Process=1 and CustId=" + CustId + "

I need follwing Values
for CustId=1

PrtyGenId CustomerName TotalAmount PurchaseDate OrderStatus
JEW101 Kotti 6000 12/10/2008 Success

for custId=2

PrtyGenId CustomerName TotalAmount PurchaseDate OrderStatus
JEW102 karthik 3000 12/13/2008 UnSuccess
JEW103 Karthik 4000 12/13/2008 Success

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-22 : 07:11:21

select t.PrtyId, t.CustName ,s.totalamount, t.PurchaseDate, t.Order from urtable t
inner join
(select prtyid,order, sum(total) as totalamount from urtable group by prtyid, order ) s on s.prtyid = t.prtyid
where t.custid = 1

select t.PrtyId, t.CustName ,s.totalamount, t.PurchaseDate, t.Order from urtable t
inner join
(select prtyid,order, sum(total) as totalamount from urtable group by prtyid, order ) s on s.prtyid = t.prtyid
where t.custid = 2

Kotti
Posting Yak Master

129 Posts

Posted - 2008-12-22 : 07:26:18

Hi bklr thanks , It's working but I need to select Distinct Prtyid.
In this query Prtyid repeated.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-22 : 07:29:00

welcome
U can use distinct keyword u can avoid the duplicate records

if u want only one record then use top clause

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources