Solved

sql get latest unique

Posted on 2011-03-25
2
274 Views
Last Modified: 2012-06-21
what to retrieve a unique count of records ... when an db update is processed an new copy of the record is actioned (to preserve this historic data) ... so for example, the same account details may appear 3 or 4 times already ...but only want to count the latest copy.

there is also a DateTimeStamp column available (when table created / add to db)
need to also group results by ColumnValue item (there will be a bunch of denominations) -- but the frequency each of those occur ... and then finally a grand total of Frequency at the bottom.

how do you script this in SQL please?


USE db1

SELECT COUNT(Index_ID) AS Frequency

FROM dbo.Table1
WHERE (MySearchCriteria_ID <> 123) AND .....
GROUP BY DenominationValue

then display at bottom:  GrandFreqTotal
0
Comment
Question by:amillyard
2 Comments
 
LVL 39

Accepted Solution

by:
lcohan earned 500 total points
ID: 35218557
Should be something like:


SELECT COUNT(distinct Index_ID) AS Frequency,SUM(GrandFreqTotal) as GrandFreqTotal
FROM dbo.Table1
WHERE MySearchCriteria_ID <> 123 AND .....
GROUP BY DenominationValue

0
 

Author Comment

by:amillyard
ID: 35227464
SUM(GrandFreqTotal) as GrandFreqTotal

the above line is not compiling - is this because there is an assumption there is column in table called:   GrandFreqTotal

the group by list -- how do I also display the column label  (or custom label) -- as currently there is just a set of figures (not easy to work out what each line is a result for).

i.e. the unique column value to be display (currently, the frequency of that value is being displayed)
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

777 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