How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask ATLPROG Your Own Question
ATLPROG
ATLPROG, Computer Software Engineer
Category: Programming
Satisfied Customers: 7677
Experience:  MS in IT.Several years of programming experience in Java C++ C C# Python VB Javascript HTML
44910485
Type Your Programming Question Here...
ATLPROG is online now
A new question is answered every 9 seconds

I am trying to get a distinct count of numbers in a column.

Customer Question

I am trying to get a distinct count of numbers in a column. I need it to give me the distinct number over the entire population and include the distinct number in a column.
For example
Invoice # Total each invoice Distinct # ***** Invoices
123 4 3
456 2 3
456 2 3
789 1 3
123 4 3
123 4 3
123 4 3
Submitted: 2 years ago.
Category: Programming
Customer: replied 2 years ago.
This must be doable in SQL Server Management Studio 2008R2
Customer: replied 2 years ago.
Just in case example in question is not clear, please see attached file. Thanks.
Expert:  Zabo04 replied 2 years ago.
I am working on the answer now.
Expert:  Zabo04 replied 2 years ago.
I am attaching an excel file with the two rows of formulas that I added next to the data you specified. They match so everything should be good.In row D I use the count function to count the occurences of the associated row in column A over the range of column A, the $A$3:$A$9 part, if you move or expand the used column range adjust just that part, leave the dollar signs, that way you can copy without issue.Column E sums the number of times the frequency of an invoice # ***** ***** than 0, so the distinct values.Please let me know if you have any questions.
Customer: replied 2 years ago.
I don't understand, xml file?
Customer: replied 2 years ago.
I'm looking for sql code.
Expert:  Zabo04 replied 2 years ago.
okay
Expert:  Zabo04 replied 2 years ago.
SELECT DISTINCT column_name FROM table_name, where column_name is ***** ***** are calling invoice # ***** table_name is ***** ***** of the table
Expert:  Zabo04 replied 2 years ago.
so if invoices is the name of the column in table sales it would be SELECT DISTINCT invoice FROM sales.
Customer: replied 2 years ago.
This does not work in SQL Server 2008R2 when there are multiple columns.
Expert:  Zabo04 replied 2 years ago.
it does if you run it as a function or a stored procedure
Expert:  Zabo04 replied 2 years ago.
that is SQL 98 compliant code, so 2008R2 has to run it, are you entering it directly on a table?
Customer: replied 2 years ago.
I would need a count of these invoices in total in one column and then a separate count of them individually in another column.Count Distinct doesn't work. I've tried it.
Expert:  Zabo04 replied 2 years ago.
No you can make that its own column as a subquery, but I need the rest of the query to input it in the correct location
Customer: replied 2 years ago.
Please send the exact code that you're talking about
Expert:  Zabo04 replied 2 years ago.
You have the code I do not, and the code I sent is the subquery, you do not need to do additional work, it returns only one number, so it can become its own field
Customer: replied 2 years ago.
it has to work with other lines of code in the select statement and what you're saying doesnt.
Expert:  Zabo04 replied 2 years ago.
Select invoice, (SELECT DISTINCT invoices FROM sales) AS "DistinctCount" FROM sales
Expert:  Zabo04 replied 2 years ago.
that's it, it is effectively a calculated column from a SELECT statement
Customer: replied 2 years ago.
That is not it. you have not included code for the other fields that I sent.
Expert:  Zabo04 replied 2 years ago.
I do not know what they are called, and look at the question you asked " I am trying to get a distinct count of numbers in a column. "I need to know what they are, how many, table name... I cannot create a working function if I do not have your schema
Customer: replied 2 years ago.
This is very difficult. i have searched the net over for an answer and have not even found the answer on MSDN. I just figured that maybe I missed it or something but everywhere I look, I cannot find the answer. It's not a simple query.
Customer: replied 2 years ago.
if you can just provide an answer to what I sent you then that would give me the code to follow. but that has not been provided. I don't want to harp on this. If there is a straight answer then there is and if there isn't then it isnt, I'll just keep looking.
Expert:  Zabo04 replied 2 years ago.
I need your schema or I cannot do anything more. You ask for one column in your question, I provide you a working query, you do not specify how many or what additional columns, nor your schema for me to know what table(s) I am calling from nor column names. How can I provide you an answer if you refuse to give me the information I need?
Customer: replied 2 years ago.
I asked for 2 columns in my question. I sent the attachment to show you what it should look like.
Expert:  Zabo04 replied 2 years ago.
what are the names? what table?
Customer: replied 2 years ago.
The example I gave was hypothetical.You can name tables and columns anything you'd like.Table:Invoices
Column/Field: Invoice#
Customer: replied 2 years ago.
Why has my account been charged before I get an answer that I can use?
Expert:  Zabo04 replied 2 years ago.
SELECT Orig.invoice, InvoiceCnt.InvoiceCount,(SELECT Count(*)FROM (SELECT DISTINCT invoice FROM sales)) AS "DistinctSource"FROM sales AS Orig LEFT JOIN (SELECT InvoiceOrig.invoice, count(InvoiceOrig.invoice) AS InvoiceCount FROM sales AS InvoiceOrig GROUP BY InvoiceOrig.invoice) AS InvoiceCnt ON Orig.invoice=InvoiceCnt.invoice;
Expert:  Zabo04 replied 2 years ago.
that works for me and produces your text file.
Customer: replied 2 years ago.
Column 2-the "total of each invoice" should also be a count.
Expert:  Zabo04 replied 2 years ago.
it is a count, from the second select statement, but to do that count it needs it's own select query, so you use a left outer join, and then just call it by the alias, InvoiceCount is the alias for the count and the right select query is InvoiceCnt
Expert:  Zabo04 replied 2 years ago.
then you join the two queries based on invoice, because I did a group by to get the correct count, so there are not the same number of records, which is why it needs its own select query
Expert:  Zabo04 replied 2 years ago.
to work a problem like this it is best to break it into three select queries, then put them together, because the last column is only a count, it can be done as a calculated field, it will be the same for all rows regardless of key, so just runt he select with an alias. The second column is unique to each invoice, but not each primary key, so it needs to because it's own select statement and become the right column in a left outer join, which will print all rows in the left table or query and match based on the columns selected in the On portion of the join to all values in the right table or query.

Related Programming Questions