Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how to get count from another table Expand / Collapse
Author
Message
Posted Sunday, July 29, 2012 10:36 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 19, 2012 5:27 AM
Points: 6, Visits: 12
Hello...

I need report whose 4 fields are from one table sat id, name, type.

And for each type, need total count of records from another table which also has one of the fields as type... say table with fields type, amount, date ....

So how can I design such report? I need report with fields id, name, type, count...
Post #1337057
Posted Monday, July 30, 2012 12:31 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:12 AM
Points: 13,612, Visits: 10,493
Something like this?

SELECT ID, Name, Type, CountType = COUNT(Type)
FROM Table1 t1 INNER JOIN Table2 t2 ON t1.Type = t2.Type
GROUP BY ID, Name, Type

This code is a best guess, as there's not much information in your question to start from.
Please read the first link in my signature on how to post questions on a forum. It explains how to post table DDL and how to post sample data.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1337071
Posted Monday, July 30, 2012 2:34 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 31, 2014 7:15 PM
Points: 1,129, Visits: 1,163
Koen Verbeeck (7/30/2012)
Something like this?

SELECT ID, Name, Type, CountType = COUNT(Type)
FROM Table1 t1 INNER JOIN Table2 t2 ON t1.Type = t2.Type
GROUP BY ID, Name, Type



create a dataset using the above code as a reference.


~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one
Post #1337106
Posted Monday, July 30, 2012 8:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 19, 2012 5:27 AM
Points: 6, Visits: 12
by code for selecting count of type is not as simple as this... it is join between 2-3 tables and all.. but will try something like u said
Post #1337250
Posted Monday, July 30, 2012 10:53 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 31, 2014 7:15 PM
Points: 1,129, Visits: 1,163
can you provide the DDl and more clarify your problem ...

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one
Post #1337413
Posted Monday, July 30, 2012 11:03 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 31, 2014 7:15 PM
Points: 1,129, Visits: 1,163
can you provide the DDl and more clarify your problem ...

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one
Post #1337420
Posted Monday, July 30, 2012 9:24 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 19, 2012 5:27 AM
Points: 6, Visits: 12
thax.. it is solved now.. thank you for your replies
Post #1337643
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse