serophous serophous - 3 months ago 12
MySQL Question

SQL getting list of records that make up an Average

First, I'm relatively new to SQL, so please bare with me.

I'm working on a project for a report where already a query is displaying information from some tables.

For example:


prod_id | name | sale_price |
1 | chair | 2
1 | chair | 4
2 | table | 5
2 | table | 10

And lets say the query is this:

Select name
,COUNT(*) as totalRecords
,AVG(sale_price) as Priceaverage
from dbo.business_table
group by name, prod_id

with that displayed on the page, a user can click a view tag beside that row, and a query will run to grab the list of records that make up that average. So if I select chair, the query will then look to grab the two records that make up that average and display them.

How can I do this?


Just for fun, you could pass the detail and parse it on the client side via JavaScript. No need to make the second trip

Declare @business_table table (prod_id int,name varchar(50),sale_price money)
Insert Into @business_table values

;with cteBase as (
    Select name
          ,totalRecords = COUNT(*)
          ,Priceaverage = AVG(sale_price) 
     From  @business_table A
     Group By name, prod_id
Select A.*
 From  cteBase A
 Cross Apply (Select Detail=Stuff((Select ',' + cast(sale_price as varchar(25))
                                     From  @business_table 
                                     Where prod_id=A.prod_id
                                     For XML Path ('')),1,1,'') ) B


name    prod_id totalRecords    Priceaverage    Detail
chair   1       2               3.00            2.00,4.00
table   2       2               7.50            5.00,10.00