?
Solved

Count the number of One to Many - Many side,

Posted on 2011-03-14
6
Medium Priority
?
368 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 57

Accepted Solution

by:
HainKurt earned 800 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

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

741 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