Solved

sql get latest unique

Posted on 2011-03-25
2
276 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 40

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

Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Help Required 3 117
date diff with Fiscal Calendar 4 75
Re-appearing SQL Server Agent jobs 7 42
SQL Server Shrink hurting performance? 4 37
Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

685 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