Counting against column value

Question

Post reply

Counting against column value

Ian.Donaldson

SSC Rookie

Points: 32
More actions
July 23, 2012 at 5:55 am

#259294

Hello All, A little stuck I am trying to get a table to show some information but also need to count against values which may not be there example I am doing a distinct select statement for a column in a table for the entire database however I also need to do another select query to then check against that value and see how many times that value has been used in a date period. So Column A will have the distinct values then trying to say if value in Column A has been used then how many times has that been used. Hope I am making sense not sure how to word this.

Viewing 7 posts - 1 through 6 (of 6 total)

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

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

Hi

Welcome to SSC.

Would you be so kind as to read the second link in my signature below on posting code and data for the best help?

With the information you provide from that article, we can create a mock setup and generate a solution for you.

Thanks

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

What Anthony is saying that is that we can't really help you because what you posted is not a complete question. We need ddl (create table statement), sample data (insert statements) and desired output based on the sample data. Obfuscate the real data and slim down the table appropriately. It does take some time but you will be rewarded with tested, accurate and fast code.

I think if you went back and read your question you would realize there is nowhere near enough information to answer this question.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Gazareth One Orange Chip Points: 27737 More actions · Answer 3

Hi Ian,

As far as I can tell, you'll need something like a SUM with a CASE, like this:

SELECT ColumnA,

SUM(CASE WHEN ColumnB BETWEEN StartDate AND EndDate THEN 1 ELSE 0 END)

FROM Table

GROUP BY ColumnA

Although, from your description, I suspect it's a little more complicated than that.

As Anthony & Sean have said, if you can provide the information asked for, it'll be a lot easier for us to help 🙂

Cheers

Gaz

Lokesh Vij SSChampion Points: 10836 More actions · Answer 4

To start with, please post some DDL and DML; example below

DDL

CREATE TABLE abc

( col1 INT NULL,

col2 CHAR(1) NULL,

....

)

Go

DML

INSERT INTO abc

VALUES

(1,'a',....),

(2,'b',....),

(3,'c',....),

.....

and don't forget to put the expected result...

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

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

Ian.Donaldson (7/23/2012)
Hello All, A little stuck I am trying to get a table to show some information but also need to count against values which may not be there example I am doing a distinct select statement for a column in a table for the entire database however I also need to do another select query to then check against that value and see how many times that value has been used in a date period. So Column A will have the distinct values then trying to say if value in Column A has been used then how many times has that been used. Hope I am making sense not sure how to word this.

Are the distinct values also constrained by the date range, or is the count of the values only constrained by the date range?

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

Here are two potential solutions, but as you haven't answered any questions, no idea which one may be the one you want or if it is something completely different.

SELECT

mt.ColA,

COUNT(*)

FROM

dbo.MyTable mt

WHERE

mt.ADateColumn >= @StartDate AND

mt.ADateColumn < @EndDate

GROUP BY

mt.ColA;

-- or

SELECT

mt.ColA,

SUM(CASE WHEN mt.ADateColumn >= @StartDate AND

mt.ADateColumn < @EndDate

THEN 1

ELSE 0

END)

FROM

dbo.MyTable mt

GROUP BY

mt.ColA;