Solved

# Count the number of One to Many - Many side,

Posted on 2011-03-14
365 Views
I have 2 tables with a one to many connection
I need to know the # of Manys on the Many Side

Items table,
ItemID , Desc

History table
HistoryID, ItemID, Solddate

I need to now the # of times they appear in the History table,
for Stat reason
I just need a count do not need the Itemid

Results Like this

23   100    ( 23 Items show  100 times in Hisotry)
10   99      ( 10 Items show  99 times in Hisotry)
1     8      ( 1 Item shows  8 times in Hisotry)

0
Question by:yahoolane
[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

LVL 13

Expert Comment

ID: 35132708
Try this and let us know if it works for you.

SELECT count(*) [COUNTS]
FROM Items INNER JOIN History ON
Items.itemID=History.ItemID
GROUP BY History.ItemID
0

LVL 1

Author Comment

ID: 35132823
LIONKING,
No, I need to two column answer,

your query just give me a list of  counts of items
1
2
3
1
1
5
0

LVL 55

Accepted Solution

Huseyin KAHRAMAN earned 200 total points
ID: 35132865
try this

select count(1) item, cnt from (
select ItemID, count(1) cnt from history group by ItemID
) x
group by cnt

0

LVL 41

Expert Comment

ID: 35132960
Are you looking for this?
``````SELECT ItemID,
COUNT(* ) [COUNTS]
FROM Items
INNER JOIN History
ON Items.itemID = History.ItemID
GROUP BY History.ItemID
``````
0

LVL 1

Author Closing Comment

ID: 35133459
Would have been great you if explained how it worked.
0

Expert Comment

ID: 35133583
This query should be helpful :
select
count(id_cnt) as Items,
sum(id_cnt) as Times
from
(
select
count(ItemID) as id_cnt
from History
group by ItemID
) tab;
0

## Featured Post

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
###### Suggested Courses
Course of the Month2 days, 19 hours left to enroll

#### 695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.