Solved

SQL Select - Query to Summarize Records

Posted on 2016-09-21
3
60 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 65

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 65

Expert Comment

by:Jim Horn
ID: 41809479
Thanks.
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

733 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