Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Aggregate functions

Posted on 2014-09-04
Medium Priority
116 Views
Hi,

I have the following table and data. This is a list of stores together with the product code held at the store. So there are multiple entries for each store equivalent to the number of products they hold.
I would like to be able to return a list of the StoreNo that have a quantity of products broken down by those that have 0-200 products, 201-400, 401-600, etc
StoreNo       ProductNo
3456            234
3456            456
3456            765
3456            101
3456            201
3456            112
4687            123
4687               990
4687            234

Any help would be appreciated
Many thanks
0
Question by:Morpheus7
[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
• 3
• 2

LVL 66

Expert Comment

ID: 40303527
>...that have a quantity of products broken down by those that have 0-200 products, 201-400, 401-600, etc
Give us a quickie mockup of how you'd like the return set to look like.
Chance are there's plenty of ways to pull this off.
0

Author Comment

ID: 40303535
Hi,
I would like to return the list of stores using the 0-200, etc  products as a heading.
0

LVL 66

Accepted Solution

Jim Horn earned 2000 total points
ID: 40303610
A data mockup would have been better.  Either way, for starters give this a whirl..

SELECT StoreNo,
Sum(CASE WHEN ProductNo >=0 AND ProductNo <=200 THEN ProductNo ELSE 0 END)  as ProductNo_0_200,
Sum(CASE WHEN ProductNo > 200 AND ProductNo <= 400 THEN ProductNo ELSE 0 END)  as ProductNo_200_400,
Sum(CASE WHEN ProductNo > 400 AND ProductNo <= 600 THEN ProductNo ELSE 0 END)  as ProductNo_400_600
FROM YourTable
GROUP BY StoreNo

Couple articles that may help you (yes this is a shameless plug)
SQL Server GROUP BY Solutions
Beware of Between Note the use of > and <=
0

Author Comment

ID: 40303832
Many thanks, that's great.
0

Author Closing Comment

ID: 40303838
That's great, many thanks
0

## Featured Post

Question has a verified solution.

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

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 ?
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
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 combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
###### Suggested Courses
Course of the Month4 days, 16 hours left to enroll