how to get count from another table

Question

Post reply

how to get count from another table

hetal1502

SSC Journeyman

Points: 80
More actions
July 29, 2012 at 10:36 pm

#278737

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...

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 1

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.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

demonfox SSCertifiable Points: 6289 More actions · Answer 2

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 :ermm:

hetal1502 SSC Journeyman Points: 80 More actions · Answer 3

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

demonfox SSCertifiable Points: 6289 More actions · Answer 4

can you provide the DDl and more clarify your problem ...

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:

demonfox SSCertifiable Points: 6289 More actions · Answer 5

can you provide the DDl and more clarify your problem ...

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:

hetal1502 SSC Journeyman Points: 80 More actions · Answer 6

thax.. it is solved now.. thank you for your replies