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]:
idobjectidtypecreatedate
1016132011-03-04 18:02:59.103
1116132011-03-04 18:04:48.877
12150822011-03-10 13:40:37.927
13150822011-03-10 13:41:00.833
141452011-04-26 18:50:14.387
151452011-04-26 18:50:50.700
151412011-04-26 18:50:50.700
151432011-04-26 18:50:50.700
this is what I want the output to be:
results:
objectidtypecnt
16132
150822
1452
1411
1431
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

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

WayneS SSC Guru Points: 95461 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

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

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

1I01RESIDENTIAL 5/8"585/8 INCH METERABDULLAH, KAREEM

1I01RESIDENTIAL 5/8"585/8 INCH METERABITUA, MARIA

1I01RESIDENTIAL 5/8"585/8 INCH METERABREGO TRUCKING

1I01RESIDENTIAL 5/8"585/8 INCH METERACEVEDO, ADELITA

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