Solved

SQL to multiply values on each row and then find the average

Posted on 2008-06-26
4
1,298 Views
Last Modified: 2010-04-21
Hi there,

I have a table Customer with the following details

AcctID,  Name, Ticket, Values

1            Bob       3           20
2            Bob       3           10
3           Bob        4            5
4           Bob        5          10
5           Bob        5          10
6           Karen     6           2

What I need to do is write a SQL query to multiply the values together for each ticket and then return the Average Value per user(Name). - The Average Value is the multiplied values for each ticket divided by the number of tickets.

Please can you help me write the SQL query to return the average values per user.

Many thanks.
0
Comment
Question by:ihatelag
  • 2
  • 2
4 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
you mean:
select name, avg(Ticket * Values)
 from customer
group by name

Open in new window

0
 

Author Comment

by:ihatelag
Comment Utility
I need to multiply the figures in the Value column not Column Ticket and Column Values,

For example  for ticket 3 - bob has 10 * 20 in the 'Values' column  so ticket 3 = 200 from 1 ticket
then we take each ticket he has run the above formulae and then get the avarage value after multiplying the values.

Only if the query was just as simple as you wrote it ;)
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
it's as simple as that, with a "small" math trick:
set nocount on

declare @t table (acctID int ,  Name varchar(30), Ticket int, [Values] int)

insert into @t values(1            ,'Bob',       3         ,  20 )

insert into @t values(2            ,'Bob',       3         ,  10)

insert into @t values(3           ,'Bob',        4         ,   5)

insert into @t values(4           ,'Bob',        5         , 10)

insert into @t values(5           ,'Bob',        5         , 10)

insert into @t values(6           ,'Karen',     6          , 2)
 

-- step 1 to show the products:

select name, ticket, cast( exp(sum(log([values]))) as int) p

from @t

group by name, ticket
 
 

-- step 2 to average by user

select name, avg(p) average_value, count(*) count_tickets, sum(p) sum_product

from (

select name, ticket, cast( exp(sum(log([values]))) as int) p

from @t

group by name, ticket

) l

group by name

Open in new window

0
 

Author Closing Comment

by:ihatelag
Comment Utility
I wasn't able to apply all of the query as the table didn't just contain the lines of the example, but I used a neat the step 1 and step 2 to good use. Many thanks for your help.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Determine next b-weekly date 12 52
ASP SQL Syntax Duplicate Key 7 64
MS SQL Backup 24 69
Help with SQL Query 23 39
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…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

763 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

13 Experts available now in Live!

Get 1:1 Help Now