CODEWITHSUNDEEP
sqlpostgresql
dipl0
dipl0

Reputation: 1077

Select Statement in PostgreSQL Duplicate Item

So i'm pretty bad at SQL however I was wondering whether anyone would be able to help me with this SQL query i've been crafting for a postgresql database .

select darkweb.site_title, count(*) from darkweb WHERE online ='t' GROUP BY darkweb.site_title HAVING count(*) > 1 ORDER BY count(*) DESC;

This had the output :

 No title                                                                                                                                                                                                                                                                               |  1773
 100x Your Coins in 24 Hours - Officially Hidden Service Anonymous                                                                                                                                                                                                                      |   319
 403 Forbidden                                                                                                                                                                                                                                                                          |   284
 Index of /                                                                                                                                                                                                                                                                             |   215
 Thank you guys!                                                                                                                                                                                                                                                                        |   182
 404 Not Found                                                                                                                                                                                                                                                                          |   155
 SecureDrop | Protecting Journalists and Sources                                                                                                                                                                                                                                        |   108
 Clone CC : No.1 Trusted onion site for Cloned Credit Card. $2000/$5000 balance available                                                                                                                                                                                               |    77
                                                                                                                                                                                                                                                                                        |    76
 Red Room                                                                                                                                                                                                                                                                               |    63
 DDos Challenge                                                                                                                                                                                                                                                                         |    52
 Dir                                                                                                                                                                                                                                                                                    |    51
 Hacker | Cyber Crime Solution                                                                                                                                                                                                                                                          |    51
 Deep Web Hosting | Secured and Anonymous | Linux PHP hosting 100MB and Unlimited Bandwidth                                                                                                                                                                                             |    51
 BKA - Seizure Banner                                                                                                                                                                                                                                                                   |    50
 TorLinks | .onion Link List The Hidden Wiki Deep Web Onion Urls Onionland Tor linklist                                                                                                                                                                                                 |    44
 ONIONLIST - SAFE .ONION LINKS LISTING                                                                                                                                                                                                                                                  |    44

output a: Ideally the output i'd like is the amount of site_titles where there is more than 1 count.

output b: I'd also like a total of all the counts (results of count(*)) in one number.

This is so that I can eventually use these two outputs (outside of sql), to subtract output a from output b in order to calculate the amount of duplicate sites.

Please let me know if there's any way in which I can be clearer on this or in which I can help.

Upvotes: 0

Views: 66

Answers (1)

Rob Streeting
Rob Streeting

Reputation: 1735

You can achieve what you need by making your query above into a sub-query and using a COUNT() to get output A and using a SUM() to get output B:

select count(*) as a, sum(amount) as b from
(
    select darkweb.site_title, count(*) as amount from darkweb WHERE online ='t' GROUP BY darkweb.site_title HAVING count(*) > 1
) site_title_counts

Note though that this will give you the sum of all counts where count > 1 - not sure if that is what you want. If you need the sum of all counts including those that are 1, then you would need to do that in a separate query:

select sum(amount) as b from
(
    select darkweb.site_title, count(*) as amount from darkweb WHERE online ='t' GROUP BY darkweb.site_title
) site_title_counts

Upvotes: 1

Related Questions