get unique entries and count? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

get unique entries and count?

Author

Topic

tech_1
Posting Yak Master

129 Posts

Posted - 2011-10-20 : 09:39:29

if I have a table:

ID
ProductID
Type (varchar)
Value

And the definition of Type is (literally):

"coat", "jeans", "t-shirts"

how can I get the various list of Types, grouped ?

data example:

quote:

COAT | 1.00 | PID1
COAT | 1.00 | PID2
COAT | 1.12 | PID1
JEANS | 3.00 | PID5
JEANS | 3.45 | PID1
SOCKS | 2.00 | PID7
SOCKS | 2.00 | PID2

I want to get each Type (Coat, jeans, socks) and show which of these have duplicates.

in the above list, we know that for COAT, we have PID1 and PID2 with the same value

for SOCKS, we know PID7 and PID2 have the same value.

so i need a list of each type, for the ones where the values are duplicate

so I would get the results as follows:

COAT | 1.00 | PID1
COAT | 1.00 | PID2
SOCKS | 2.00 | PID7
SOCKS | 2.00 | PID2

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2011-10-20 : 10:27:57

You want to maybe start with an sub-query that does a count of the types for each value as below but unsure of the rest just yet:


select COUNT(t.Type) AS C1, t.Value
from Types t
group by t.Value


C1      Value
==      ===== 
2	1
1	1.12
2	2
1	3
1	3.45

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2011-10-20 : 10:34:01

I think this is what you are looking for:

select t.*
from
(select COUNT(t.Type) AS CountTypes, t.Value
from Types t
group by t.Value) c, Types t
where t.Value = c.Value
and c.CountTypes > 1

But my join is not strictly on pk and also it shows the value as a single figure instead of like a floating point?

tech_1
Posting Yak Master

129 Posts

Posted - 2011-10-20 : 11:36:54

hmm. ok, here is the real table but cannot seem to execute the query against it.

DeviceHistory
ID (Guid)
DevID (Guid)
Type (varchar)
Value (varchar)

I want to display records where there is more than 1 value of the same data but also the devID's to which these are relating to

DBAPBFL
Starting Member

11 Posts

Posted - 2011-10-20 : 11:57:15

dear tech_1, it would be so much easier if you post create and insert statements for your table and sample data...

tech_1
Posting Yak Master

129 Posts

Posted - 2011-10-21 : 05:09:33

sure.

http://64.85.165.102/dataScript.txt

just create a blank DB and run the script

tech_1
Posting Yak Master

129 Posts

Posted - 2011-10-21 : 05:26:04

this *seems* to work:

quote:

SELECT DISTINCT(DeviceId), ValueText1, ValueText2
FROM [DeviceHistory]
WHERE [ValueText2] IN
(
SELECT ValueText2
FROM DeviceHistory
WHERE ValueText1 = 'isamirn' AND ValueText2 <> ''
GROUP BY ValueText2
HAVING COUNT(*) > 1
)
ORDER BY ValueText2

not sure if it is the right/best way to do it and if there are any potential issues

but now how to make it so that I am not hardcoding isamirn but instead using the unique values found for ValueText1 (so we will have say isamirn, simid, ver etc... and I want to do the same query as above but for each of the items in the list)

tech_1
Posting Yak Master

129 Posts

Posted - 2011-10-21 : 05:37:03

hmm. almost there. one of the records is actually duplicated for DeviceID and ValueText2 but I want to eliminate that also ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-21 : 05:40:02

[code]
SELECT dh.DeviceId, dh.ValueText1, dh.ValueText2
FROM [DeviceHistory] dh
INNER JOIN(
SELECT ValueText1,ValueText2
FROM DeviceHistory
WHERE ValueText2 <> ''
GROUP BY ValueText1,ValueText2
HAVING COUNT(*) > 1
)t
ON t.ValueText2 = dh.ValueText2
AND t.ValueText1 = dh.ValueText1
ORDER BY dh.ValueText2
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-21 : 05:46:33

is this what you want ?


select	h.*
from	DeviceHistory h
	inner join
	(
		select	DeviceId, ValueText1, ValueText2
		from	DeviceHistory
		where	ValueText2	<> ''
		group by DeviceId, ValueText1, ValueText2
		having count(*) > 1
	) d	on	h.DeviceId	= d.DeviceId
		and	h.ValueText1	= d.ValueText1
		and	h.ValueText2	= d.ValueText2

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-21 : 05:48:19

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-21 : 05:52:03

whats the unique valued column (pk) of yourtable?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2011-10-21 : 07:17:11

quote:
Originally posted by visakh16

SELECT dh.DeviceId, dh.ValueText1, dh.ValueText2
FROM [DeviceHistory] dh
INNER JOIN(
SELECT ValueText1,ValueText2
FROM DeviceHistory
WHERE ValueText2 <> ''
GROUP BY ValueText1,ValueText2
HAVING COUNT(*) > 1
)t
ON t.ValueText2 = dh.ValueText2
AND t.ValueText1 = dh.ValueText1
ORDER BY dh.ValueText2

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Where do you get the valuetext1/2 columns Visak?

DeviceHistory
ID (Guid)
DevID (Guid)
Type (varchar)
Value (varchar)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-21 : 08:21:48

quote:
Originally posted by Grifter

quote:
Originally posted by visakh16

SELECT dh.DeviceId, dh.ValueText1, dh.ValueText2
FROM [DeviceHistory] dh
INNER JOIN(
SELECT ValueText1,ValueText2
FROM DeviceHistory
WHERE ValueText2 <> ''
GROUP BY ValueText1,ValueText2
HAVING COUNT(*) > 1
)t
ON t.ValueText2 = dh.ValueText2
AND t.ValueText1 = dh.ValueText1
ORDER BY dh.ValueText2

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Where do you get the valuetext1/2 columns Visak?

DeviceHistory
ID (Guid)
DevID (Guid)
Type (varchar)
Value (varchar)

G

From OPs posted code on 10/21/2011 : 05:26:04

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources