Solved

SQL Select - Query to Summarize Records

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

Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

734 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