Perplexing Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Perplexing Query

Author

Topic

direrayne
Starting Member

30 Posts

Posted - 2010-10-24 : 15:05:18

I need some help with what i believe is a complex query.

I have the following table.

Name TrxQuantity Price Receipt Number TrxCount
Elizabeth Lara 1 35 13S00000000000913 1
Rashida Gopie 1 45 13S00000000000912 0
Rashida Gopie 2 60 13S00000000000912 1
Raquel Rankin 1 50 13S00000000000911 0
Raquel Rankin 1 95 13S00000000000911 1
Raquel Rankin 1 55 13S00000000000910 0
Raquel Rankin 1 25 13S00000000000910 0
Raquel Rankin 1 25 13S00000000000910 1
Elizabeth Lara 1 35 13S00000000000909 1
Sheryl Abad 1 30 13S00000000000908 0
Sheryl Abad 1 25 13S00000000000908 1
Raquel Rankin 1 55 13S00000000000907 0
Raquel Rankin 1 25 13S00000000000907 1
Sheryl Abad 1 30 13S00000000000906 1
Rashida Gopie 1 45 13S00000000000905 0

I have to make a query to provide the following results,
1) The sales persons avg number of items Per transaction (or receipt), and the avg dollar amount per transaction.

The confusion for me is that each item has its own line, the trxcount column has a "1" for the first line on the receipt and a "0" for each additional line. To make this worse as you may see on line 3 of the data, if two of the same items were purchase on the same receipt then it still has only one line but marked and trxqunatity of "2" or whatever the amount of items where.
I am completely confused on how to even attempt this query.

Sachin.Nand

2937 Posts

Posted - 2010-10-24 : 15:10:47

What should be the o/p?

PBUH

direrayne
Starting Member

30 Posts

Posted - 2010-10-24 : 15:16:59

quote:
Originally posted by Sachin.Nand

What should be the o/p?

PBUH

What is o/p?

direrayne
Starting Member

30 Posts

Posted - 2010-10-24 : 15:16:59

quote:
Originally posted by Sachin.Nand

What should be the o/p?

PBUH

What is o/p?

Sachin.Nand

2937 Posts

Posted - 2010-10-24 : 15:31:18

I mean output.Can you post the sample output for the data?

PBUH

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-10-24 : 15:35:57

o/p output

direrayne
Starting Member

30 Posts

Posted - 2010-10-24 : 15:46:49

quote:
Originally posted by Sachin.Nand

I mean output.Can you post the sample output for the data?

PBUH

It should be something like this.


Sales_Name   Avg$_perTrx  AvgItem_perTrx
Elizabeth Lara	805	1
Elizabeth Lara	320	9
Mayuree Bull	1035	1
Mayuree Bull	760	20
Raquel Rankin	1395	0
Raquel Rankin	550	17
Rashida Gopie	465	1
Rashida Gopie	700	13
Sheryl Abad	590	2
Sheryl Abad	800	18

This would be easy except each receipt is a transaction not each line.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2010-10-24 : 18:31:52

need to group by Name and Receipt.

elsasoft.org

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources