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

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

  • 2
  • 2
2 Solutions
Evan CutlerCommented:
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?  
Scott PletcherSenior 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.
Scott PletcherSenior DBACommented:
GROUP BY Name, date, time
Evan CutlerCommented:
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.
the_b1ackfoxAuthor 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.

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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