Solved

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

Posted on 2008-10-29
4
174 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
  • 2
4 Comments
 
LVL 73

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 73

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 500 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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
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…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

747 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

12 Experts available now in Live!

Get 1:1 Help Now