By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,317 Members | 1,339 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,317 IT Pros & Developers. It's quick & easy.

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

P: 16
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.

Attached Images
File Type: jpg Count_SQL.jpg (32.1 KB, 1545 views)
File Type: jpg Updated_Tables.jpg (45.0 KB, 1098 views)
Jan 9 '13 #1

✓ answered by 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 :
Expand|Select|Wrap|Line Numbers
  1. SELECT   GETDATE() AS [ReportDate]
  2.        , tRT.Risk_Code
  3.        , COUNT(*) AS [MemberCount]
  4. FROM     ([RiskTable] AS tRT
  5.          INNER JOIN
  6.          [Area_Table] AS tAT
  7.   ON     tRT.Area_Code=tAT.Area_Code)
  8.          LEFT JOIN
  9.          [MemberTable] AS tMT
  10.   ON     tAT.SubArea=tMT.SubArea)
  11. GROUP BY tRT.Risk_Code

Share this Question
Share on Google+
6 Replies


ck9663
Expert 2.5K+
P: 2,878
What do you have so far?

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

Happy Coding!!!


~~ CK
Jan 9 '13 #2

NeoPa
Expert Mod 15k+
P: 31,768
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.
Jan 10 '13 #3

P: 16
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):
Expand|Select|Wrap|Line Numbers
  1. UPDATE Area_Report
  2. SET MemberCount = (SELECT COUNT (*) FROM MemberTable WHERE SubArea = Area_table.Area_Code AND UpdateDate = DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0))
  3. FROM Area_Report
  4. INNER JOIN MemberTable ON Area_Table.SubArea = MemberTable.SubArea
  5. WHERE MemberTable.SubArea = Area_table.SubArea
  6. 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 :-(
Jan 10 '13 #4

NeoPa
Expert Mod 15k+
P: 31,768
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 :
Expand|Select|Wrap|Line Numbers
  1. SELECT   GETDATE() AS [ReportDate]
  2.        , tRT.Risk_Code
  3.        , COUNT(*) AS [MemberCount]
  4. FROM     ([RiskTable] AS tRT
  5.          INNER JOIN
  6.          [Area_Table] AS tAT
  7.   ON     tRT.Area_Code=tAT.Area_Code)
  8.          LEFT JOIN
  9.          [MemberTable] AS tMT
  10.   ON     tAT.SubArea=tMT.SubArea)
  11. GROUP BY tRT.Risk_Code
Jan 10 '13 #5

P: 16
Thanks NeoPa, I somehow managed to get the script using yours as my guide.
Jan 10 '13 #6

NeoPa
Expert Mod 15k+
P: 31,768
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.
Jan 10 '13 #7

Post your reply

Sign in to post your reply or Sign up for a free account.