Solved

Count the number of One to Many - Many side,

Posted on 2011-03-14
6
365 Views
Last Modified: 2012-05-11
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
Comment
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
6 Comments
 
LVL 13

Expert Comment

by:LIONKING
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

by:yahoolane
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

by:
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 41

Expert Comment

by:Sharath
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

Open in new window

0
 
LVL 1

Author Closing Comment

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

Expert Comment

by:pakachele
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

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.

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.

Join & Ask a Question