45650

Simple SQL statement

Question:

I've been over-thinking this too much. Let's say I have a table TEST(refnum VARCHAR(5))

|refnum| -------- | 12345| | 56873| | 63423| | 12345| | 56873| | 12345|

I want my "view" to look something along the lines of this

|refnum| count| --------------- | 12345| 3 | | 56873| 2 |

So the requirements are that the count for each refnum has to be > 1. I'm having a little trouble wrapping my head around this one. Thank you in advance for the help.

Answer1:

Unless I am missing something, this looks like a simple

select refnum, count(*) from test group by refnum having count(*) > 1

Answer2:

select refnum, count(*) from table group by refnum

Answer3:

This is the SQL Server version:

CREATE VIEW vRefnumCounts AS SELECT refnum, count(1) as [count] FROM test GROUP BY refnum HAVING count(1) > 1 SELECT * FROM vRefnumCounts ORDER BY refnum

You said "view", but now I'm thinking you meant in the result set sense...

Recommend

  • Build Matrix of Comparisons in SQl Server
  • c++ using primitive types as a base class
  • Accessing the variables from a PHP Anonymous Function
  • ASPNetCore MVC Routing Let Server Handle Specific Route
  • Wrapping a c#/WPF GUI around c++/cli around native c++
  • Creating NSCollectionView with datasource programatically
  • Configure nginx to return different files to different authenticated users with the same URI
  • Time out Error in send mail
  • How to log in to an HTTPS website with Jsoup?
  • PDO error when wrong host name
  • Is there a way to dynamically embed PDF Files in a JSP pulled from the file system?
  • Why isn't obj.style.left = “200px”; working in this code?
  • Switch to popup in python using selenium
  • Simple linked list-C
  • Who propagate bugfixes across branches (corporate development)?
  • Tell Git to stop prompting me for conflicts when none really exist?
  • Web.config system.webserver errors
  • ADO and msqli connections very slow
  • why xml file does not aligned properly after append the string in beginning and end of the file usin
  • How can the INSERT … ON CONFLICT (id) DO UPDATE… syntax be used with a sequence ID?
  • Problems compiling files using JOGL
  • htaccess add www if not subdomain, if subdomain remove www
  • HTTP/2 streams vs HTTP/1.1 connections
  • JSON response opens as a file, but I can't access it with JavaScript
  • Python urlparse: small issue
  • Jquery UI tool tip close icon
  • Display issues when we change from one jquery mobile page to another in firefox
  • Deselecting radio buttons while keeping the View Model in synch
  • Accessing IRQ description array within a module and displaying action names
  • JSON with duplicate key names losing information when parsed
  • Large data - storage and query
  • Jquery - Jquery Wysiwyg return html as a string
  • Benchmarking RAM performance - UWP and C#
  • File not found error Google Drive API
  • Is it possible to post an object from jquery to bottle.py?
  • Observable and ngFor in Angular 2
  • How to Embed XSL into XML
  • UserPrincipal.Current returns apppool on IIS
  • Conditional In-Line CSS for IE and Others?
  • Net Present Value in Excel for Grouped Recurring CF