How to Count(*) certain records from multiple SQL table

[Josh Andrews]

Hi, how do I write this in sql script. I want to count the records from two or more joined tables and place the result in a new table. Attached is my tables. Thanks.
[IMGNOTHUMB]http://bytes.com/attachments/attachment/6809d1357733881/count_sql.jpg[/IMGNOTHUMB][IMGNOTHUMB]http://bytes.com/attachments/attachment/6814d1357788796/updated_tables. jpg[/IMGNOTHUMB]

[ck9663]

What do you have so far?

Just use an alias on your table and prefix the column in your query.

Happy Coding!!!


~~ CK

[NeoPa]

Josh, You shouldn't be considering saving the data thus worked out in another table. You just need to build any report on the View that determines the results as they currently stand. Also, you need to post what you already have before we can help you. This is a site rule to ensure we don't get treated simply as a free resource for doing members' work for them.

In general terms though, you would be looking at linking the [MEMBER] table and the [AREA] table on [SubArea] then aggregating the data by [Area_Code] to count the records.

[Josh Andrews]

Hi, I was still coding/testing for two linked tables then place the results to the third table for reports. The following was my code (but still I get error messages for this, sometimes no error but wrong results):

UPDATE Area_Report
SET MemberCount = (SELECT COUNT (*) FROM MemberTable WHERE SubArea = Area_table.Area_Code AND UpdateDate = DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0))
FROM Area_Report
INNER JOIN MemberTable ON Area_Table.SubArea = MemberTable.SubArea
WHERE MemberTable.SubArea = Area_table.SubArea
and AreaReport.ReportDate = DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)

But my problem is that when I try to get the third table (kindly see the second image), I really do not know how to do that. Too many errors persist. I just copy codes from other sources and try to change them (I am not really good at coding; I use some GUIs to do the scripting for me -- but this time, they produce results far from reality). I really need help :-(

[NeoPa]

Josh:
I really need help :-(

Indeed, but it's best to take note of it when you're in that situation. See my earlier post about not using a table to store calculated values in. For more on this see Database Normalisation and Table Structures. What you are considering is exactly what you are warned not to do.

On the basis that you need a properly designed SELECT query, try :

SELECT   GETDATE() AS [ReportDate]
       , tRT.Risk_Code
       , COUNT(*) AS [MemberCount]
FROM     ([RiskTable] AS tRT
         INNER JOIN
         [Area_Table] AS tAT
  ON     tRT.Area_Code=tAT.Area_Code)
         LEFT JOIN
         [MemberTable] AS tMT
  ON     tAT.SubArea=tMT.SubArea)
GROUP BY tRT.Risk_Code

[Josh Andrews]

Thanks NeoPa, I somehow managed to get the script using yours as my guide.

[NeoPa]

I'm guessing you're still building the data into a table then Josh.

Never mind. I've done my bit warning you, and all others who may read this. You may have good reasons, outside the scope of this question, for having that strange requirement, so I'll just say that I'm pleased I could be of some help, and your very welcome to it.