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
Solved

SQL Select - Query to Summarize Records

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

856 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