Solved

group by

Posted on 2006-11-22
5
252 Views
Last Modified: 2010-08-05
hi i have a query which have sales and i want it to be like where receipt no is same add the soldvalue and give me top 10 only.


select  ph.txn_date_time as DATE_TIME,
        (select branch_desc from branch where branch_id = ph.branch_id) as Branch_Name,
        ph.receipt_no as RECEIPT_NO,
     ph.assistant as logged_on_user,
        ssu.username as Served_by,
        ssu.firstname as Firstname,
        ssu.lastname as Lastname,
        psd.sold_value,
        psd.sku,
        pm.alt_code1 as Style

from    pm_trans_header ph,
        pm_trans_sku_details psd,
        ss_users ssu,
        product_master pm,
        product_detail pd

where ph.receipt_id = psd.receipt_id
and   psd.salesperson_id = ssu.id
and   psd.sku_id = pd.sku_id
and   pd.product_id = pm.prod_id
and   txn_date_time between '2006-08-01 0:00' and '2006-11-20 23:59'
--and   psd.receipt_id = 221408
--and   pm.alt_code1 like 'freda-%-%'
and   ph.flag = 1
and   ph.transaction_void = 0
and   psd.flag = 1
order by Branch_name
--and   ph.assistant = 'sm'
--and   psd.sku like '01%'
--ph.assistant, psd.salesperson_id
--select * from branch
--select * from pm_trans_sku_details
--select * from pm_trans_header

/*select * from  pm_trans_sku_details
--
--set salesperson_id = 392
where receipt_id = 221408
select * from pm_trans_header
where receipt_no = 018639

select * from ss_users
where username = 'sm'*/
0
Comment
Question by:ammartahir1978
  • 2
  • 2
5 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17994586
can u post the sample u r getting and tghe expected output
0
 

Author Comment

by:ammartahir1978
ID: 17995076
following is the result i am getting but what i want is

Recept no       Serveredby      fname            lname            Soldvalue
23588                  XYZ              A                    b                    50000




RESULT of my query:

DATE-TIME      BRANCH      RECEIPT_NO      LOGGON_USER      SERVED_BY      FNAME      LNAME      SOLD_VALUE      SKU      STYLE
8/7/06 11:43 AM      34 - WIMBLEDON      23588      EH      JozelteH      Jozelte      Herreira      488.7      02C119520005BLK073      CHL-Q-CH7027
8/7/06 11:43 AM      34 - WIMBLEDON      23588      EH      JanetteP      Janette      Purkiss      488.7      02C119520005BRO073      CHL-Q-CH7027
8/7/06 11:43 AM      34 - WIMBLEDON      23588      EH      JozelteH      Jozelte      Herreira      270.9      02CQ19520005GRE204      CL-Q-DECOLLETE868
8/7/06 11:43 AM      34 - WIMBLEDON      23588      EH      JozelteH      Jozelte      Herreira      319.5      02GK14520006GRY020      DG1-Q-F37RET
8/7/06 11:43 AM      34 - WIMBLEDON      23588      EH      JozelteH      Jozelte      Herreira      319.5      02GK07520003GRY020      DG1-Q-F2624K
8/7/06 11:43 AM      34 - WIMBLEDON      23588      EH      JozelteH      Jozelte      Herreira      421.2      02LX19520002BRO073      LAN-Q-AW5B3INTO
8/7/06 11:43 AM      34 - WIMBLEDON      23588      EH      ELENAH      ELENA      HERRANZ-GONZALEZ      306      02BAC9530005GRY083      BAJ-Q-163501
8/7/06 11:43 AM      34 - WIMBLEDON      23588      EH      ELENAH      ELENA      HERRANZ-GONZALEZ      759.6      02IM17520009BLK020      MIS-Q-EVITA
8/7/06 11:43 AM      34 - WIMBLEDON      23588      EH      ELENAH      ELENA      HERRANZ-GONZALEZ      231.3      02C207520004BLK129      CHL-Q-T513
8/7/06 11:43 AM      34 - WIMBLEDON      23588      EH      ELENAH      ELENA      HERRANZ-GONZALEZ      421.2      02GK14520005BEI020      DG1-Q-F38DET
8/7/06 11:43 AM      34 - WIMBLEDON      23588      EH      ELENAH      ELENA      HERRANZ-GONZALEZ      373.5      02FX07530002BK1130      BUR-Q-W59A12130
8/7/06 11:43 AM      34 - WIMBLEDON      23588      EH      ELENAH      ELENA      HERRANZ-GONZALEZ      1120.5      02C217520004BLK073      CHL-Q-R021
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 17995155
Try this

select
        (select branch_desc from branch where branch_id = ph.branch_id) as Branch_Name,
        ph.receipt_no as RECEIPT_NO,
          min(ssu.username) as Served_by,
        min(ssu.firstname) as Firstname,
        min(ssu.lastname) as Lastname,
        sum(psd.sold_value)Soldvalue,

from    pm_trans_header ph,
        pm_trans_sku_details psd,
        ss_users ssu,
        product_master pm,
        product_detail pd

where ph.receipt_id = psd.receipt_id
and   psd.salesperson_id = ssu.id
and   psd.sku_id = pd.sku_id
and   pd.product_id = pm.prod_id
and   txn_date_time between '2006-08-01 0:00' and '2006-11-20 23:59'

and   ph.flag = 1
and   ph.transaction_void = 0
and   psd.flag = 1
group by branch_name , receipt_no
order by Branch_name
0
 

Author Comment

by:ammartahir1978
ID: 17995194
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'branch_name'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'branch_name'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'branch_name'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'branch_name'.


these are the error i received
0
 
LVL 28

Accepted Solution

by:
imran_fast earned 500 total points
ID: 17995220
Try

select
        branch_desc  as Branch_Name,
        ph.receipt_no as RECEIPT_NO,
         min(ssu.username) as Served_by,
        min(ssu.firstname) as Firstname,
        min(ssu.lastname) as Lastname,
        sum(psd.sold_value)Soldvalue

from    pm_trans_header ph
      inner join
        pm_trans_sku_details psd on ph.receipt_id = psd.receipt_id
      inner join
        ss_users ssu on  psd.salesperson_id = ssu.id
      inner join
        product_master pm on pd.product_id = pm.prod_id
      inner join
        product_detail pd on psd.sku_id = pd.sku_id
      left outer join
      branch br on br.branch_id = ph.branch_id
where
  txn_date_time between '2006-08-01 0:00' and '2006-11-20 23:59'

and   ph.flag = 1
and   ph.transaction_void = 0
and   psd.flag = 1
group by branch_desc , receipt_no
order by branch_desc
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question