Solved

group by

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

751 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