We help IT Professionals succeed at work.

Need to return a count of Vendors by SKU as a field in a result list the skus of a single vendor.

286 Views
Last Modified: 2013-12-24
Table contains multiple Vendors with multplie SKUs.  I currently have a query that pulls on a single vendor listing only those skus which are also listed by other vendors (See attached sql statement.)  Now in addition to what gets returned I want to add a field to the result set that returns a COUNT of how many Vendors list that same SKU.  Once that gets accomplished I want to pass a parameter that will query on that count as well.  For vendor "A" list all skus that are listed by 3 or more vendors.  Right now I am getting a list of all skus that are listed by other vendors but I need a count by SKU of those vendors and later the ability to query on the count.

Thanks in advance
select *
from merchant_to_product
where fk_merchant_id = 373
AND price between 0 AND 300
AND fk_subcategory_id = 89
AND (merchant_to_product.sku IN
                          (SELECT     sku
                            FROM      merchant_to_product,merchant
                            WHERE     fk_merchant_id = merchant_id
			 AND merchant.is_active = 1					 AND fk_merchant_id <> 373))

Open in new window

Comment
Watch Question

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
I would do like this:
select mp.*
, (select count(*) from merchant_to_product,merchant
                            WHERE     fk_merchant_id = merchant_id
                   AND merchant.is_active = 1                               AND fk_merchant_id <> 373)
  other_merchant_count
from merchant_to_product p
where fk_merchant_id = 373
AND price between 0 AND 300
AND fk_subcategory_id = 89
AND (merchant_to_product.sku IN
                          (SELECT     sku
                            FROM      merchant_to_product,merchant
                            WHERE     fk_merchant_id = merchant_id
                   AND merchant.is_active = 1                               AND fk_merchant_id <> 373))

Open in new window

Author

Commented:
Thanks for your replay but the query reurns an error.
I had to change the "m" in the from line to "mp" to match the "mp.*" in the select line which killed a lot of the error but still getting the one below.
"The column prefix 'merchant_to_product' does not match with a table name or alias name used in the query."

Any help will be greatly appreciated.

Thanks
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
sorry for that... got distracted :(
select mp.*
, ( SSELECT COUNT(*)
                 FROM merchant im
                 JOIN merchant_to_product imp
                   ON imp.fk_merchant_id = im.merchant_id
                  AND imp.fk_merchant_id <> 373
                WHERE im.merchant.is_active = 1
                  AND imp.sku = mp.sku
              ) other_merchant_count
from merchant_to_product mp
where mp.fk_merchant_id = 373
AND mp.price between 0 AND 300
AND mp.fk_subcategory_id = 89
AND mp.sku IN (SELECT imp.sku
                 FROM merchant im
                 JOIN merchant_to_product imp
                   ON imp.fk_merchant_id = im.merchant_id
                  AND imp.fk_merchant_id <> 373
                WHERE im.merchant.is_active = 1
              )

Open in new window

Author

Commented:
Great!!!!  I had to make a minor adjustment in the where clause (" im.merchant.is_active" to "im.is_active") and then it returned what I wanted.

NOW,  the next step, how would I query that based on the count?  Return only those with 5 or more in the count field.

Hey thanks so much.  Question why the aliases for the tables? I apprecaite the statement but want to know why it works too.
select mp.*
, ( SSELECT COUNT(*)
                 FROM merchant im
                 JOIN merchant_to_product imp
                   ON imp.fk_merchant_id = im.merchant_id
                  AND imp.fk_merchant_id <> 373
                WHERE im.is_active = 1
                  AND imp.sku = mp.sku
              ) other_merchant_count
from merchant_to_product mp
where mp.fk_merchant_id = 373
AND mp.price between 0 AND 300
AND mp.fk_subcategory_id = 89
AND mp.sku IN (SELECT imp.sku
                 FROM merchant im
                 JOIN merchant_to_product imp
                   ON imp.fk_merchant_id = im.merchant_id
                  AND imp.fk_merchant_id <> 373
                WHERE im.is_active = 1
              ) 
Open in New Window

Open in new window

Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
> Question why the aliases for the tables?
  1. to make it clear which column comes from which table/alias (especially for any reader later)
  2. in case of (correlate) subqueries, so make sure which value is taken.
  3. for correlated subqueries, to ensure you link to the correct table/alias
>I appreciate the statement but want to know why it works too.

check out this part of the query:

select mp.*
, ( SELECT COUNT(*)
                 FROM merchant im
                  <...>
                 WHERE ... 
                   AND imp.sku = mp.sku   <<< this line correlates the subquery, and does the count only for the "mp.sku" for the current row of the outer query
  )

Open in new window

Author

Commented:
That did the trick!

Thanks a lot.  I was trying variations of what you gave over the last few hours but all without aliases hence my cry for help.  Thanks so much!

I have another question along the same lines as this query, would you want me to ask it here or post  a new one.

using the same table, return a column with a ranking based on price.  Basically sku, number of vendors(which you already accomplished), but now ranking.  Then of course passing a parameter quering rank.  Give me all skus with more than 4 vendors where I rank 2 or better.

Thanks again!!!!!!
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
with sql 2005+, ranking is "easy" with the row_number() , or rank() functions.
with sql 2000, this will be tricky

Author

Commented:
Thanks for all your help.  I'm sure I will have more questions has I dive deeper into this project.  I will post my follow up question about ranking in SQL 2000.  Thanks again and have a good night.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.