count occurrences of unique 2 column combination

Question

Post reply

count occurrences of unique 2 column combination

petervdkerk

SSCrazy

Points: 2155
More actions
May 20, 2011 at 4:36 pm

#250512

I want to select the number of occurrences in table [extlinks] with a count on unique occurrences of 2 columns: objectid and type

the unique combination objectid and type should be counted as an occurrence.

source table [extlinks]:

id objectid type createdate

10 161 3 2011-03-04 18:02:59.103

11 161 3 2011-03-04 18:04:48.877

12 1508 2 2011-03-10 13:40:37.927

13 1508 2 2011-03-10 13:41:00.833

14 14 5 2011-04-26 18:50:14.387

15 14 5 2011-04-26 18:50:50.700

15 14 1 2011-04-26 18:50:50.700

15 14 3 2011-04-26 18:50:50.700

this is what I want the output to be:

results:

objectid type cnt

161 3 2

1508 2 2

14 5 2

14 1 1

14 3 1

So, this is what I have now:

select objectid,COUNT(objectid) as clicks from extlinks group by objectid

But that counts all occurrences of objectid once, ignoring the value of the type column

Viewing 11 posts - 1 through 10 (of 10 total)

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

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

Almost there: select objectid, type, count(*) as cnt from tbl group by objectid, type

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

petervdkerk SSCrazy Points: 2155 More actions · Answer 2

petervdkerk

SSCrazy

Points: 2155

May 20, 2011 at 4:45 pm

#1328038

Thanks!

lgonzales 69796 Old Hand Points: 351 More actions · Answer 3

lgonzales 69796

Old Hand

Points: 351

June 1, 2011 at 11:22 am

#1332644

What if you need to this but the type is in another table?

WayneS SSC Guru Points: 95405 More actions · Answer 4

lgonzales 69796 (6/1/2011)

What if you need to this but the type is in another table?

JOIN to it.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

lgonzales 69796 Old Hand Points: 351 More actions · Answer 5

lgonzales 69796

Old Hand

Points: 351

June 1, 2011 at 11:37 am

#1332670

I have my tables inner join. Would I use a stmt within a stmt?

lgonzales 69796 Old Hand Points: 351 More actions · Answer 6

here is my current stmt. I need to count how many TRNCD.DESCRIPT, UMMTR.XSIZE

SELECT DISTINCT

dbo.PROPDB_UMMTR.DISTRICT, dbo.PROPDB_UMMTR.RATETYPE, dbo.PROPDB_TRNCD.DESCRIPT, dbo.PROPDB_UMMTR.XSIZE,

dbo.PROPDB_UMSIZ.DESCRIPT AS SIZEDESC, COUNT(dbo.PROPDB_UMMTR.XSIZE) AS QUANTITY, SUM(dbo.PROPDB_USAGO.CONSUMED)

AS CONSUMPTION

FROM dbo.PROPDB_UMMTR INNER JOIN

dbo.PROPDB_USAGO ON dbo.PROPDB_UMMTR.ORG_NBR = dbo.PROPDB_USAGO.ORG_NBR AND

dbo.PROPDB_UMMTR.METERKY = dbo.PROPDB_USAGO.METERKY INNER JOIN

dbo.PROPDB_TRNCD ON dbo.PROPDB_UMMTR.RATETYPE = dbo.PROPDB_TRNCD.TRNCDKY INNER JOIN

dbo.PROPDB_UMSIZ ON dbo.PROPDB_UMMTR.XSIZE = dbo.PROPDB_UMSIZ.SIZECODE INNER JOIN

dbo.PROPDB_CUST ON dbo.PROPDB_UMMTR.SVADDRKEY = dbo.PROPDB_CUST.SRVCKY INNER JOIN

dbo.PROPDB_ADDRS ON dbo.PROPDB_UMMTR.SVADDRKEY = dbo.PROPDB_ADDRS.ADKEY

WHERE (dbo.PROPDB_CUST.XSTATUS = 'AC') AND (dbo.PROPDB_CUST.CUSTNAME <> 'VACANT')

GROUP BY dbo.PROPDB_UMMTR.DISTRICT, dbo.PROPDB_UMMTR.RATETYPE, dbo.PROPDB_UMMTR.XSIZE, dbo.PROPDB_TRNCD.DESCRIPT,

dbo.PROPDB_UMSIZ.DESCRIPT

HAVING (dbo.PROPDB_UMMTR.DISTRICT = 1)

ORDER BY dbo.PROPDB_UMMTR.RATETYPE, dbo.PROPDB_UMMTR.XSIZE

LutzM SSC Guru Points: 107049 More actions · Answer 7

Since I don't have any table def and sample data this is just a guess. Close enough?

SELECT



  UMMTR.DISTRICT,

  UMMTR.RATETYPE,

  TRNCD.DESCRIPT,

  UMMTR.XSIZE,

  UMSIZ.DESCRIPT AS SIZEDESC,

  COUNT(UMMTR.XSIZE) AS QUANTITY,

  SUM(USAGO.CONSUMED) AS CONSUMPTION

FROM

  dbo.PROPDB_UMMTR UMMTR

INNER JOIN dbo.PROPDB_USAGO USAGO

ON

  UMMTR.ORG_NBR = USAGO.ORG_NBR

  AND UMMTR.METERKY = USAGO.METERKY

INNER JOIN dbo.PROPDB_TRNCD TRNCD

ON

  UMMTR.RATETYPE = TRNCD.TRNCDKY

INNER JOIN dbo.PROPDB_UMSIZ UMSIZ

ON

  UMMTR.XSIZE = UMSIZ.SIZECODE

INNER JOIN dbo.PROPDB_CUST CUST

ON

  UMMTR.SVADDRKEY = CUST.SRVCKY

INNER JOIN dbo.PROPDB_ADDRS ADDRS

ON

  UMMTR.SVADDRKEY = ADDRS.ADKEY

WHERE

  ( CUST.XSTATUS = 'AC' )

  AND ( CUST.CUSTNAME <> 'VACANT' )

GROUP BY

  UMMTR.DISTRICT,

  UMMTR.RATETYPE,

  TRNCD.DESCRIPT,

  UMMTR.XSIZE,

  UMSIZ.DESCRIPT

HAVING

  ( UMMTR.DISTRICT = 1 )

ORDER BY

  UMMTR.RATETYPE,

UMMTR.XSIZE

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

lgonzales 69796 Old Hand Points: 351 More actions · Answer 8

lgonzales 69796

Old Hand

Points: 351

June 1, 2011 at 2:04 pm

#1332774

sorry, but I don't see a difference than from what I posted.

you only ommitted the dbo.propdb in front of the table names.

LutzM SSC Guru Points: 107049 More actions · Answer 9

lgonzales 69796 (6/1/2011)

sorry, but I don't see a difference than from what I posted.

you only ommitted the dbo.propdb in front of the table names.

I aliased the tables to improve readability and I also removed the DISTINCT.

The query looks like it will return "some" results. Wheher those results are what you're looking for or not is still unknown.

Please remember: neither we know your business case nor can we look over your shoulder to see what you see. All we have is what you've posted so far...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

lgonzales 69796 Old Hand Points: 351 More actions · Answer 10

The Statement below provides an example of the data I have below which shows about 4000 records. However, my second statment below the first data sample, provides higher count of 58 records. Below the second statement is a data sample. Please let me know if you provide me some assistance. What I am trying to do is create a query that counts all different sizes of meters and provides the consumption for each size. As in my 58 example, the system contains data for different sizes. As a test, I am only working with the 58 size.

SELECT dbo.PROPDB_UMMTR.DISTRICT, dbo.PROPDB_UMMTR.RATETYPE, dbo.PROPDB_TRNCD.DESCRIPT, dbo.PROPDB_UMMTR.XSIZE AS SIZE,

dbo.PROPDB_UMSIZ.DESCRIPT AS SIZEDESC, dbo.PROPDB_CUST.CUSTNAME

FROM dbo.PROPDB_UMMTR INNER JOIN

dbo.PROPDB_USAGO ON dbo.PROPDB_UMMTR.ORG_NBR = dbo.PROPDB_USAGO.ORG_NBR AND

dbo.PROPDB_UMMTR.METERKY = dbo.PROPDB_USAGO.METERKY INNER JOIN

dbo.PROPDB_TRNCD ON dbo.PROPDB_UMMTR.RATETYPE = dbo.PROPDB_TRNCD.TRNCDKY INNER JOIN

dbo.PROPDB_UMSIZ ON dbo.PROPDB_UMMTR.XSIZE = dbo.PROPDB_UMSIZ.SIZECODE INNER JOIN

dbo.PROPDB_CUST ON dbo.PROPDB_UMMTR.SVADDRKEY = dbo.PROPDB_CUST.SRVCKY INNER JOIN

dbo.PROPDB_ADDRS ON dbo.PROPDB_UMMTR.SVADDRKEY = dbo.PROPDB_ADDRS.ADKEY

GROUP BY dbo.PROPDB_UMMTR.DISTRICT, dbo.PROPDB_UMMTR.RATETYPE, dbo.PROPDB_TRNCD.DESCRIPT, dbo.PROPDB_UMSIZ.DESCRIPT,

dbo.PROPDB_CUST.CUSTNAME, dbo.PROPDB_UMMTR.XSIZE

HAVING (dbo.PROPDB_UMMTR.DISTRICT = 1) AND (dbo.PROPDB_UMMTR.XSIZE = 58)

ORDER BY dbo.PROPDB_UMMTR.RATETYPE, dbo.PROPDB_CUST.CUSTNAME

1 I01 RESIDENTIAL 5/8" 58 5/8 INCH METER ABDULLAH, KAREEM

1 I01 RESIDENTIAL 5/8" 58 5/8 INCH METER ABITUA, MARIA

1 I01 RESIDENTIAL 5/8" 58 5/8 INCH METER ABREGO TRUCKING

1 I01 RESIDENTIAL 5/8" 58 5/8 INCH METER ACEVEDO, ADELITA

SELECT dbo.PROPDB_UMMTR.DISTRICT, dbo.PROPDB_UMMTR.RATETYPE, dbo.PROPDB_TRNCD.DESCRIPT, dbo.PROPDB_UMMTR.XSIZE,