Query Question 2

[azzazzello]

I have a fairly siple query, like:

SELECT id, alias, random, count(alias) as num FROM table WHERE ... GROUP by alias;

There are, however, duplicates in the table in regard to id and alias. Specifically, more that one row can have identical id and alias. I want to have such rows only counted ONCE. Is there a simple was to do this, preferably without a JOIN?

 

[TonyGroves]

Not quite sure what you mean there. Can you give us a sample table and expected result set?

 

[azzazzello]

ok...

SELECT id, alias, random, FROM table WHERE ...;

+-------+------------+-------------------+
| id | alias | random |
+-------+------------+-------------------+
| 2210 | 1111 | Micro |
| 2210 | 1111 | Micro |
| 2211 | 1111 | Micro |
| 2211 | 1111 | Micro |
| 2223 | 1111 | Micro |
| 2056 | 2222 | Micro |
| 2064 | 2222 | Micro |
| 2064 | 2222 | Micro |
| 2278 | 2222 | Micro |
+-------+------------+-------------------+

I need to count the number of distinct aliases, however, in addition, I also only want to count records with identical ID field only once.

i can count them with:

SELECT id, alias, random, count(alias) as num FROM table WHERE ... GROUP by alias;

but that will count the duplicates too. I tried putting DISTINCT in there, but I think count(alias) of GROUP BY is somehow throwing it off. Hopefully, this is more clear now.

 

[snowboardr]

Maybe try..

?

SELECT DISTINCT id, DISTINCT alias, random, count(alias) as num FROM table WHERE ... GROUP by alia

www.sitesd.com
ASP WEB DEVELOPMENT

 

[azzazzello]

I do not think DISTINCT can be applied to separate columns, and that's the issue. It is applied to ALL columns, meaning that it works if DISTINCT is across all rows. Furthermore, I can even do without random column. I can do

SELECT DISTINCT id, alias FROM table WHERE ...;

and I will get rid of the annoying duplicate rows! HOWEVER, once I do

SELECT id, alias count(alias) as num FROM table WHERE ... GROUP by alias;

the duplicates are back Somehow, either the count throws distinct off, or else count is executed before distinct is

 

[swampBoogie]

What result do you expect given the sample above? 2?

select count(distinct alias) from t

I also only want to count records with identical ID field only once

what do you mean by that?

 

[snowboardr]

I believe he means he wants id and alieas to both be distinct values...

www.sitesd.com
ASP WEB DEVELOPMENT

 

[azzazzello]

YES!!! count(distinct ID) from table works!!! Thank you! I did not know you can stick distinct there like that

 

[azzazzello]

Ok, sorry guys, I have to revisit this again. Let me try to be as clear as possible, because I do not seem to be explaining myself well.

SELECT id, alias, FROM table WHERE ...;

+-------+------------+
| id | alias |
+-------+------------+
| 2210 | 1111 |
| 2210 | 1111 |
| 2211 | 1111 |
| 2211 | 1111 |
| 2223 | 1111 |
| 2056 | 2222 |
| 2064 | 2222 |
| 2064 | 2222 |
| 2223 | 2222 |
+-------+------------+

What I need after some sort of *count(?) as calls*/GROUP query is the following result

+------------+---------+
| alias | calls |
+------------+---------+
| 1111 | 3 |
| 2222 | 3 |
+------------+---------+

This represents unique calls across the aliases. A row that has the same id AND alias as another row is considered a duplicate, and should NOT be counted twice.
The above solution by swampBoogie, though helpful in another case I have, does not work here. I cannot do DISTINCT (id), because that would view 5th and 9th records in the above sample table as duplicates and count them only once. They do, however, have different aliases. I guess I need something like distinct(id,alias), but that is obviously not possible.

 

[r937]

select alias
     , count(distinct id) as calls
  from yourtable
group by alias

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[azzazzello]

That, unfortunately, does not work for the reason I mentioned . The following 2 entries will be considered duplicates by your query, r937:

+----_--+-------_----+
| id | alias |
+-------+------------+
| 2223 | 1111 |
| 2223 | 2222 |
+-------+------------+

I want only those rows that have identical id AND alias to be considered duplicates.

 

[TonyGroves]

How about:
[tt]
SELECT
alias, COUNT(DISTINCT CONCAT(id,alias)) calls
FROM t
GROUP BY alias
[/tt]

 

[r937]

i'm sorry, azzy, but i tested it, and it does so work

create table azzazzello 
( id integer 
, alias integer
);
insert into azzazzello values
 ( 2210, 1111 )
,( 2210, 1111 )
,( 2211, 1111 )
,( 2211, 1111 )
,( 2223, 1111 )
,( 2056, 2222 )
,( 2064, 2222 )
,( 2064, 2222 )
,( 2223, 2222 )   
;
select alias
     , count(distinct id) as calls
  from azzazzello
group by alias
;

alias calls
 1111  3
 2222  3

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[azzazzello]

Sorry guys, couldn't reply right away as the network at my work gave out. R937, you are right! it works for this example! I think, again, I did a bad job of presenting a simplified case that reflects what I am actually doing, as this query works for the example that I presented, but NOT for the actual stuff I need to do . However, TonyGroves' suggestion works!! Very clever way of doing that, and exactly what I need - this will allow me to weed out duplicates based on any number and combination of columns!! Thank you! R937, I appreciate your help too just for some reason though it works for this example, it does not work for what I need to do, and I am not sure how they are different. But thank you anyway

 

[r937]

when you GROUP BY x, then every row in the group will have the same value of x

that's how GROUP BY works

thus, there must be exactly the same number of DISTINCT CONCAT(x,y) values as DISTINCT y

the two solutions give the same results

please have a look for some of "the actual stuff I need to do"

i'm betting you won't be able to find examples where tony's solution works and mine doesn't

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[azzazzello]

OK, I found the problem, Rudy. It was my fault, again . Whereas I need ID and ALIAS, I am actually grouping (and need to group) by something else...I was looking at a grouping of a different query . This is why the concat is working for me, but your query isn't - I am not grouping by alias.