Solved

group by

Posted on 2006-11-22
5
249 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 Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
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.

757 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now