Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
|
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old February 3rd, 2005, 03:07 AM
Registered User
 
Join Date: Feb 2005
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to jdebelen Send a message via Yahoo to jdebelen
Default Getting item count from another table

I have 2 tables, Shops and Products. For each shop, there are several products that are assigned to them. Meaning, WalMart has 4 types of products and K-Mart has 2.


-------------------
 ShopID | ShopName
-------------------
    A | WalMart
    B | K-Mart
-------------------

--------------------------------
 ProductID | Name | ShopID
--------------------------------
    1 | Apple | A
    2 | Banana | A
    3 | Mango | A
    4 | Grapes | A
    5 | Orange | B
    6 | Lemon | B
--------------------------------


I want to make a select statement that will come up with the following result:


-------------------------
  ShopName | Quantity
-------------------------
  WalMart | 4
  K-Mart | 2
-------------------------


... where for each shop, the number of products assigned to the shop is counted and placed under the Quantity column. And please note that ShopNames can go as up to, let's say, 5000 shops. So--- CASE or IF-ELSE statements won't do.

Please help! Thanks!


 
Old February 3rd, 2005, 05:27 AM
Friend of Wrox
 
Join Date: Dec 2004
Posts: 307
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Vadivel Send a message via Yahoo to Vadivel
Default

Sample Table Structure and Data

Create table ShopMaster
(
ShopId char(1),
ShopName varchar(25)
)
Go
Insert into ShopMaster values ('A','WalMart')
Insert into ShopMaster values ('B','K-Mart')
Go

Create table ProductInfo
(
ProductId int,
ProductName varchar(25),
ShopId char(1)
)
Go

Insert into ProductInfo values (1,'Apple','A')
Insert into ProductInfo values (2,'Banana','A')
Insert into ProductInfo values (3,'Mango','A')

Insert into ProductInfo values (4,'Grapes','A')
Insert into ProductInfo values (5,'Orange','B')
Insert into ProductInfo values (6,'Lemon','B')
Go

Query which would fetch you the expected result

Select ShopName, Quantity=Count(P.ProductName)
From ShopMaster S, ProductInfo P
Where S.ShopId = P.ShopId
Group by ShopName

Best Regards
Vadivel

MVP ASP/ASP.NET
http://vadivel.thinkingms.com





Similar Threads
Thread Thread Starter Forum Replies Last Post
Row Count in each table ayazhoda Access VBA 0 April 15th, 2008 04:31 PM
UPDATE from COUNT(*) in another table. jlrolin SQL Language 1 March 20th, 2007 01:57 AM
count item urbanaccess Access 1 January 16th, 2007 12:33 PM
count the number of rows in the table crmpicco Javascript How-To 4 February 2nd, 2005 12:58 AM
count child table rows melvik C# 6 January 25th, 2004 01:39 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.