Counting Query Results that have the same value in multiple columns

Question

Counting Query Results that have the same value in multiple columns

dustinprevatt

Old Hand

Points: 383
More actions
June 20, 2012 at 9:28 am

#271513

I would like to write a query that only shows results when certain columns display the same result a certain amount of times.
I'm just not sure how to use the count function in the select statement when i only want to show the results of the column when certain critera is met.
Basically I only want to see results when the values in multiple columns occur more than 4 times.
Any Suggestions?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

dustinprevatt (6/20/2012)
I would like to write a query that only shows results when certain columns display the same result a certain amount of times.
I'm just not sure how to use the count function in the select statement when i only want to show the results of the column when certain critera is met.
Basically I only want to see results when the values in multiple columns occur more than 4 times.
Any Suggestions?

Based on what you have provided, nope, not a clue. How about providing us with a concrete example of what you are trying to accomplish including DDL for the table(s), sample data, and expected results based on the sample data. All of this should be done with readily consumable code that all we need to do is cut, paste and run in SSMS.

If you need help with this, please read the first article I reference below in my signature block.

dustinprevatt Old Hand Points: 383 More actions · Answer 2

Here is a rough example of what i have.

SELECT Distinct Column1, Column2, Column3, Column4, Column5,

Column6, Column10, Column7, Column8, Column9,

FROM Table.1 INNER JOIN

Table.c ON table = table INNER JOIN

Table.b ON table = table INNER JOIN

Table.a ON table = table INNER JOIN

table ON table = table

where Column6> '2012-06-18 '

GROUP BY Column1, Column2, Column5,Column3,

Column4, Column6, Column10,

Column7, Column8, Column9

HAVING COUNT (Column1, 2, 3, 4, 5) >= 4

Order BY Column1, Column2, Column3, Column5,

Column4, Column10,

Column6, Column7, Column8, Column9

I only want to get back records where columns 1-5 have the same value listed >= 4times

I hope this helps :unsure:

Maybe I am suppose to do something for those particular columns in the select statement....not sure

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

dustinprevatt (6/20/2012)
Here is a rough example of what i have.
SELECT Distinct Column1, Column2, Column3, Column4, Column5,
Column6, Column10, Column7, Column8, Column9,
FROM Table.1 INNER JOIN
Table.c ON table = table INNER JOIN
Table.b ON table = table INNER JOIN
Table.a ON table = table INNER JOIN
table ON table = table
where Column6> '2012-06-18 '
GROUP BY Column1, Column2, Column5,Column3,
Column4, Column6, Column10,
Column7, Column8, Column9
HAVING COUNT (Column1, 2, 3, 4, 5) >= 4
Order BY Column1, Column2, Column3, Column5,
Column4, Column10,
Column6, Column7, Column8, Column9
I only want to get back records where columns 1-5 have the same value listed >= 4times
I hope this helps :unsure:
Maybe I am suppose to do something for those particular columns in the select statement....not sure

Still not enough. Nothing above really helps figure out what you are trying to accomplish. I need DDL (CREATE TABLE statement(s)) for the tables involved, sample data (as a series of INSERT INTO statements) for the table(s) involved, the expected results based on the sample data you provide.

Please, read the first article I reference below in my signature block. It will walk you through everything you need to do to post the information we need to help you.