Solved

sql get latest unique

Posted on 2011-03-25
2
271 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
Comment Utility
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
Comment Utility
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

763 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now