incorrect data

[bouwob]

SELECT distinct (t.Domain), count(t.Domain) as TOTAL, sum(case when t.status = 'Succeeded' then 1 else 0 end) as SUCCEEDED
FROM table1 t
WHERE t.ID='SOMETHING'
group by t.Domain, t.status


Domain/total/Success
AR 1 0
AR 1 1
BR 1 0
HOMEOFFICE 1 0
HOMEOFFICE 2 0
HOMEOFFICE 4 0
HOMEOFFICE 18 18
JP 2 0
MX 1 0
MX 13 13

now when grabing all of the netries fro homeoffice I get a list that looks like this


domain status
HOMEOFFICE Succeeded
HOMEOFFICE Succeeded
HOMEOFFICE Succeeded
HOMEOFFICE Succeeded
HOMEOFFICE Succeeded
HOMEOFFICE Succeeded
HOMEOFFICE Succeeded
HOMEOFFICE Accepted
HOMEOFFICE Succeeded
HOMEOFFICE Succeeded
HOMEOFFICE No Status
HOMEOFFICE Succeeded
HOMEOFFICE Failed
HOMEOFFICE Accepted
HOMEOFFICE Succeeded
HOMEOFFICE Failed
HOMEOFFICE Succeeded
HOMEOFFICE Succeeded
HOMEOFFICE Succeeded
HOMEOFFICE Succeeded
HOMEOFFICE Succeeded
HOMEOFFICE Succeeded
HOMEOFFICE Succeeded
HOMEOFFICE Waiting
HOMEOFFICE Waiting
HOMEOFFICE Waiting
HOMEOFFICE Waiting

so now my question is why is the distinct not working for the domain. Instead it is giving me a list of each domain and each status.

Any theories on why this is happening?


The list should look like this

Domain/total/Success
AR 2 1
BR 1 0
HOMEOFFICE 25 18
JP 2 0
MX 14 13

tia

 

[kaht]

so now my question is why is the distinct not working for the domain

It is, but you are grouping your data by domain [!]AND[/!] status.

In the homeoffice domain example that you're showing above, you have the following:

1 with status: No Status
2 with status: Accepted
2 with status: Failed
4 with status: Waiting
18 with status: Succeeded

since you are pulling the distinct domain and count, it's pulling 1, 2, 4, and 18 (as denoted by my list above)


-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson
[small]<P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <.</B> bites again.[/small]

 

[bouwob]

removing the order by t.status fixed the problem.

 

[kaht]

Removing an [!]order by[/!] shouldn't change the result set, only what order the results are returned. Are you sure you didn't mean [!]group by[/!]?

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson
[small]<P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <.</B> bites again.[/small]

 

[SQLSister]

I personally would remove the distinct as it is unneeded when you have a group by.

"NOTHING is more important in a database than integrity." ESquared