Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

group by

Posted on 2006-11-22
5
Medium Priority
?
283 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying 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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
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.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

721 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