Solved

group by

Posted on 2006-11-22
5
254 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

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.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

679 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