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

x
?
Solved

Return results ordered by count sort results from a select SQL Query

Posted on 2008-10-29
4
Medium Priority
?
180 Views
Last Modified: 2010-04-21
I have a webapp..

A User clicks on a US State on a map, the State represents a two digit abbreviation of the state they clicked. In the database there are 1000's of locations each one has a two digit state abriviation that indicates what state they are from.  

It is easy to return a list of locations by state
(Select * from Database where State = "Clicked State" Order By Whatever)

What I need is more complicated than that...each location entry has 10 columns  (Product1 to Product10) and if that location has the product in stock, it has a "Y" in that field.

I need to return a list ordered by which location has the most "Y".  So the users would be shown who has the most products in stock.

Database: Stores
Database Table: Locations
Database fields:
Company, Address, State, Zip, Product1, Product2, Product3,  Product4, Product5, Product6, Product7, Product8, Product9, Product10, Product10

How can I do this via a select SQL Statement?
0
Comment
Question by:EGormly
[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
4 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 22832645
select * from locations
order by
(case when product1='Y' then 1 else 0 end +
case when product2='Y' then 1 else 0 end +
case when product3='Y' then 1 else 0 end +
case when product4='Y' then 1 else 0 end +
case when product5='Y' then 1 else 0 end +
case when product6='Y' then 1 else 0 end +
case when product7='Y' then 1 else 0 end +
case when product8='Y' then 1 else 0 end +
case when product9='Y' then 1 else 0 end +
case when product10 = 'Y' then 1 else 0 end
)

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 22832653
the above sorts from least 'Y' count to most
or, to do sort from most Y to least....

select * from locations
order by
(case when product1='Y' then 1 else 0 end +
case when product2='Y' then 1 else 0 end +
case when product3='Y' then 1 else 0 end +
case when product4='Y' then 1 else 0 end +
case when product5='Y' then 1 else 0 end +
case when product6='Y' then 1 else 0 end +
case when product7='Y' then 1 else 0 end +
case when product8='Y' then 1 else 0 end +
case when product9='Y' then 1 else 0 end +
case when product10 = 'Y' then 1 else 0 end
) desc
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 1500 total points
ID: 22832674
Here you go:


select * from 
(select *,
case when Product1='Y' then 1 else 0 end 
+ case when Product2='Y' then 1 else 0 end 
+ case when Product3='Y' then 1 else 0 end 
+ case when Product4='Y' then 1 else 0 end 
+ case when Product5='Y' then 1 else 0 end 
+ case when Product6='Y' then 1 else 0 end 
+ case when Product7='Y' then 1 else 0 end 
+ case when Product8='Y' then 1 else 0 end 
+ case when Product9='Y' then 1 else 0 end 
+ case when Product10='Y' then 1 else 0 end as InStockCount
from Locations
where State = 'PA'
) a
order by InStockCount DESC

Open in new window

0
 

Author Closing Comment

by:EGormly
ID: 31511247
this didn't work for me no matter how I sliced it up.. but I get what it means so that is helpful
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

715 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