Count the number of One to Many - Many side,

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)


LVL 1
yahoolaneAsked:
Who is Participating?
 
HainKurtConnect With a Mentor Sr. System AnalystCommented:
try this

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

0
 
LIONKINGCommented:
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
 
yahoolaneAuthor Commented:
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
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
SharathData EngineerCommented:
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
 
yahoolaneAuthor Commented:
Would have been great you if explained how it worked.
0
 
pakacheleCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.