Solved

# SQL data manipulation

Posted on 2011-10-26
208 Views
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
Question by:the_b1ackfox

LVL 9

Expert Comment

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

LVL 68

Assisted Solution

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

LVL 68

Expert Comment

GROUP BY Name, date, time
0

LVL 9

Accepted Solution

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

LVL 9

Author Closing Comment

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

### Suggested Solutions

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…