Menu
I'm trying to refine a query that I am currently using:
SELECT `puid`, COUNT(DISTINCT `droid_v`) AS DROID, COUNT(DISTINCT `sig_v`) AS sig, SUM(NoExt) AS hits FROM temp GROUP BY `puid` And I need to get it to only count droid_v where droid_V is greater than 0. Is it possible to condition the count in this way? At the moment, it is counting zero values as countable, and I can't really change the zeros to null values.
I do not need to know the count value for droid_V = 0, I only need to count it if it has a number greater than 0. That number will always be either 0, 1, 2, 3, or 4.
I have tried:
SELECT `puid`, COUNT(DISTINCT CASE WHEN `droid_v` > 0 THEN 1 END) AS DROID, COUNT(DISTINCT `sig_v`) AS sig, SUM(`NoExt`) AS hits FROM temp GROUP BY `puid` But this gives a binary result (either 0 or 1) not the count I am expecting.
example output =
puid DROID sig hits No PUID 1 1 252 x-fmt/92 1 5 1008 anticipated output:
puid DROID sig hits No PUID 1 1 252 x-fmt/92 3 5 1008 Sample data:
id;puid;droid_v;sig_v;speed;Ext;NoExt;tally 1;"No PUID";"3";"v13";"SLOW";"0";"63";"63" 2;"x-fmt/92";"3";"v13";"SLOW";"63";"0";"63" 3;"x-fmt/92";"3";"v37";"SLOW";"63";"63";"126" 4;"x-fmt/92";"3";"v45";"SLOW";"63";"63";"126" 5;"x-fmt/92";"3";"v49";"SLOW";"63";"63";"126" 6;"x-fmt/92";"3";"v50";"SLOW";"63";"63";"126" 7;"x-fmt/92";"5";"v13";"SLOW";"63";"0";"63" 8;"No PUID";"5";"v13";"SLOW";"0";"63";"63" 9;"x-fmt/92";"5";"v37";"SLOW";"63";"63";"126" 10;"x-fmt/92";"5";"v45";"SLOW";"63";"63";"126" 11;"x-fmt/92";"5";"v49";"SLOW";"63";"63";"126" 12;"x-fmt/92";"5";"v50";"SLOW";"63";"63";"126" 13;"No PUID";"6";"v13";"FAST";"0";"63";"63" 14;"x-fmt/92";"6";"v13";"SLOW";"63";"0";"63" 15;"No PUID";"6";"v13";"SLOW";"0";"63";"63" 16;"x-fmt/92";"6";"v13";"FAST";"63";"0";"63" 17;"x-fmt/92";"6";"v37";"SLOW";"63";"63";"126" 18;"x-fmt/92";"6";"v37";"FAST";"63";"63";"126" 19;"x-fmt/92";"6";"v45";"FAST";"63";"63";"126" 20;"x-fmt/92";"6";"v45";"SLOW";"63";"63";"126" 21;"x-fmt/92";"6";"v49";"FAST";"63";"63";"126" 22;"x-fmt/92";"6";"v49";"SLOW";"63";"63";"126" 23;"x-fmt/92";"6";"v50";"FAST";"63";"63";"126" 24;"x-fmt/92";"6";"v50";"SLOW";"63";"63";"126" 
983
The count() function with the GROUP BY clause is used to count the data which were grouped on a particular attribute of the table.
To count the number of rows, use the id column which stores unique values (in our example we use COUNT(id) ). Next, use the GROUP BY clause to group records according to columns (the GROUP BY category above). After using GROUP BY to filter records with aggregate functions like COUNT, use the HAVING clause.
SELECT [DISTINCT] COUNT([DISTINCT] IF(<condition>, <expression>, NULL)) AS alias_name FROM your_table_name; The syntax shows that: COUNT() function includes IF() function, which has a condition specified. If the <condition> is true, then the count will be calculated based on <expression> passed.
A number, expression, cell reference, or text string that determines which cells will be counted. For example, you can use a number like 32, a comparison like ">32", a cell like B4, or a word like "apples". COUNTIF uses only a single criteria. Use COUNTIFS if you want to use multiple criteria.
If droid_v can only be 0, 1, 2, 3, or 4, then COUNT(DISTINCT) will never return more than 5, since there are only five possible values. Is that what you want? If so, then try this:
SELECT puid, COUNT(DISTINCT CASE WHEN droid_v > 0 THEN droid_v ELSE 0 END) - 1 AS droid /* -1 for the case where droid_v is 0 */ , COUNT(DISTINCT sig_v) AS sig , SUM(NoExt) AS hits Update: Oops, sorry, the above is not quite right as there might not be a zero. It should be:
SELECT puid, COUNT(DISTINCT CASE WHEN droid_v > 0 THEN droid_v END) AS droid If, on the other hand, you want a count of all the rows where droid_v > 0, then I think you want this:
SELECT puid, SUM(CASE WHEN droid_v > 0 THEN 1 ELSE 0 END) AS droid , COUNT(DISTINCT sig_v) AS sig , SUM(NoExt) AS hits Hope this helps.
166
Just use a sum
so if you want to group something check this out
select city, sum( case when gender = 'male' then 1 else 0 end ) as male, sum( case when gender = 'female' then 1 else 0 end ) as female from person group by city simple as this :D
21
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
