SQL data manipulation

Posted on 2011-10-26
Last Modified: 2012-05-12
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

Question by:the_b1ackfox
    LVL 9

    Expert Comment

    by:Evan Cutler
    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?  
    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.
    LVL 68

    Expert Comment

    GROUP BY Name, date, time
    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.
    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.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    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…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now