Solved

SQL Select - Query to Summarize Records

Posted on 2016-09-21
3
73 Views
Last Modified: 2016-09-21
I need some help with a SQL Server query that will summarize data and compute an average average like shown below.

I need the output to look like this:

Supplier ID     Base Model Avg    Image Avg   New Feature Avg
   1999                  1                             .5                .5
   8412                 .75                           1                 .5

My table looks like this:
 Supplier ID      SKU #      Base Model Nbr     Image     New Feature 01
   1999                1               32                            Yes          [blank]
   1999                2               81                            No           Purple Belt
   8412                19             [blank]                     Yes          [blank]
   8412                21             64                            Yes          Blue Shoe
   8412                8               21                             Yes          
   8412                9               22                             Yes         Red Shoe

I need to summarize the data in this table by Supplier and compute the following averages:
 
Base Model Avg  = Nbr of SKUs for each supplier that have a Base Model Nbr /  Nbr of SKUs for supplier

Image Avg  = Nbr of SKUs for each supplier that have a Image  /  Nbr of SKUs for supplier

New Feature 01 Avg  = Nbr of SKUs for each supplier that have a New Feature 01 value  /  Nbr of SKUs for supplier
0
Comment
Question by:tmajor99
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 41809213
< Air code.  I do my own stunts too. >
SELECT 
   Supplier, 
   SUM(CASE WHEN COALESCE([Base Model Nbr], '') <> '' THEN 1 ELSE 0 END) / 
   (COUNT(COALESCE([Base Model Nbr], '')) * 1.0) as [Base Model Avg], 
   -- copy-paste the above line and change names to reflect Image Avg and New FeatureAvg
FROM YourTable
GROUP BY Supplier

Open in new window

0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 41809268
SELECT [Supplier ID],
    CAST(SUM(CASE WHEN [Base Model Nbr] > '' THEN 1 ELSE 0 END) * 1.0 /
        COUNT(*) AS decimal(5, 2)) AS [Base Model Avg],
    CAST(SUM(CASE WHEN [Image] = 'Yes' THEN 1 ELSE 0 END) * 1.0 /
        COUNT(*) AS decimal(5, 2)) AS [Image Avg],
    CAST(SUM(CASE WHEN [New Feature] > '' THEN 1 ELSE 0 END) * 1.0 /
        COUNT(*) AS decimal(5, 2)) AS [New Feature Avg]
FROM table_name
GROUP BY [Supplier ID]
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 41809479
Thanks.
0

Featured Post

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

632 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