CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
user2133404
user2133404

Reputation: 1857

Select column values not present in group by clause

I have a table consisting of customer purchase records having following columns

CUSTOMER  TRANSDATE  SALENUM  STORE  TRANSTYP  PRICE  
--------  --------- --------- ----- --------- ------
  1     12-FEB-2013    777      O     CASH      7.99
  1     12-FEB-2013    777      O     CASH      6.99
  1     12-FEB-2013    778      O     CARD      9.17
  2     23-APR-2013    987      D     CASH      7.65
  1     15-MAY-2013    1098     T     CARD      2.35

I want to aggregate over salenum i,e for each salenum, i want the total price, as well as the store, transtyp, transdate,customer values as these are same for a particular salenum

However if i use 

select customer,transdate,salenum,store,transtyp,sum(price) from table1 group by salenum

Its obviously saying not a valid group by value. How to get the desired result?

SAMPLE RESULT: 

CUSTOMER  TRANSDATE  SALENUM  STORE  TRANSTYP  PRICE  
--------  --------- --------- ----- --------- ------
  1     12-FEB-2013    777      O     CASH      15.98
  1     12-FEB-2013    778      O     CARD       9.17

Upvotes: 0

Views: 2282

Answers (2)

sentil kumar
sentil kumar

Reputation: 95

SELECT CUSTOMER,TRANSDATE,SALENUM,STORE,TRANSTYP,SUM(PRICE)
FROM TABLE1
GROUP BY CUSTOMER,TRANSDATE,SALENUM,STORE,TRANSTYP
ORDER BY CUSTOMER

I using the order by clause because it will be in order and also in sequence.

Upvotes: 0

Blorgbeard
Blorgbeard

Reputation: 103467

All non-aggregated columns should be in the group by:

select customer,transdate,salenum,store,transtyp,sum(price)
from table1 
group by customer,transdate,salenum,store,transtyp

Upvotes: 1

Related Questions