Solved

Count the number of One to Many - Many side,

Posted on 2011-03-14
6
361 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 51

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 eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 
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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Help needed in sql query 4 28
sql server cross db update 2 20
UPDATE JOIN multiple tables 5 21
using a SELECT statement in a  THEN  of a CASE statement. 5 14
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
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…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

749 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