serophous serophous - 2 months ago 7
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:

dbo.business_table

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
,prod_id
,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?

Answer

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
(1,'chair',2),
(1,'chair',4),
(2,'table',5),
(2,'table',10)

;with cteBase as (
    Select name
          ,prod_id
          ,totalRecords = COUNT(*)
          ,Priceaverage = AVG(sale_price) 
     From  @business_table A
     Group By name, prod_id
)
Select A.*
      ,B.Detail
 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

Returns

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