• Status: Solved
• Priority: Medium
• Security: Public
• Views: 225

# SQL data manipulation

Hello SQL ninjas...   I have data in the following format:

Name  variety  date  time  row  size    varvalue
Red7   Hc         Xx     xx     1     6.6     5.231
Red7   Hc         Xx     xx     2     6.4     7.111
Red7   Hc         Xx     xx     3     6.8     6.372
Red7   Hr         Xy     xy     1     5.3     4.657
Red7   Hr         Xy     xy     2     5.2     5.012
Red7   Hr         Xy     xy     3     6.2     4.839

Assuming that Xx matches Xx, and xx matches xx and so on for date and time,
Is there any way for me to get the following data per name+variety?

Row count.        Avg.      Stdevp.     Avg.       Stdevp
Red7   Hc         Xx     xx           3.               6.6        5.892        5.7         4.789

Avg = average
Stdevp = standard deviation for a population

0
the_b1ackfox
• 2
• 2
2 Solutions

Commented:
yes, but the qusetion is what you want to group?
Is date time to provide a summery at the end of the day?
Per week? Month?
0

Senior DBACommented:
SELECT Name, date, time,
sum(case when variety = 'Hc' then 1 else 0 end) as [Row count],
sum(case when variety = 'Hc' then size else 0 end) / sum(case when variety = 'Hc' then 1 else 0 end)  as [Avg.],
sum(case when variety = 'Hr' then size else 0 end) / sum(case when variety = 'Hr' then 1 else 0 end)  as [Avg.]

I will have to research the std dev part more ... I'm not familiar with doing that.
0

Senior DBACommented:
GROUP BY Name, date, time
0

Commented:
well, I would use variety to group by, in case Xx matches Xy by accident.

Select Name, date, time, variety, count (variety), avg(size), stdev(size), avg(varvalue), stdev(varvalue)
from TABLE
group by name, variety;

if you dont' like the response, switch variety and name in group by.
0

Author Commented:
Arcee213, talk about a home run!  That worked!  here is what I ended up with:

Select [Name], variety,  date, [time], avg([size]), stdevp([size]), avg(varvalue), stdevp(varvalue)
from TABLE
group by [name], variety, date, [time]

This was my first time dealing with those functions.  ScottPletcher: I felt there is something to learned from your entry as well so I am awarding you some points too.
0

## Featured Post

• 2
• 2
Tackle projects and never again get stuck behind a technical roadblock.