Solved

SQL Select - Query to Summarize Records

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
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 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…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

863 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

20 Experts available now in Live!

Get 1:1 Help Now