Professional Web Applications Themes

count problem - MySQL

Hi, I have a problem counting the occurence of a number within 3 spefic columns for one table. What I want to do is to count the number of occurence of a number (60 for example) for each of the three columns starting by row*. I'm able to count it seperatly but can't find the correct way to do it for the three in a single query. Anyone can help me out? tid gid row1 row2 row3 1 1 20 72 0 2 1 60 0 0 3 1 77 73 0 4 1 60 0 0 5 1 77 ...

  1. #1

    Default Count problem

    Hi,

    I have a problem counting the occurence of a number within 3 spefic
    columns for one table.

    What I want to do is to count the number of occurence of a number (60
    for example) for each of the three columns starting by row*. I'm able
    to count it seperatly but can't find the correct way to do it for the
    three in a single query. Anyone can help me out?

    tid gid row1 row2 row3
    1 1 20 72 0
    2 1 60 0 0
    3 1 77 73 0
    4 1 60 0 0
    5 1 77 60 74
    6 1 74 60 21
    7 1 77 60 73
    8 2 83 9 63
    9 2 1 26 0
    10 3 10 0 0


    SELECT COUNT(*) as col1result FROM theTable
    WHERE row1=60

    ANS: 2

    SELECT COUNT(*) as col2result FROM theTable
    WHERE row2=60

    ANS: 3

    SELECT COUNT(*) as col3result FROM theTable
    WHERE row3=60

    ANS: 0

    I'd like to have

    col1result col2result col3result
    ----------------------------------------
    2 3 0

    In a single query.

    Thanks in advance.
    John
    Jonathan Grenier Guest

  2. #2

    Default Re: Count problem

    Thanks Vishal.

    It works fine!

    Now I have another problem. I'd like to count the occurence of all the
    distinct numbers present in the three columns (except 0 which is in fact
    a dummy) and show the results in that fashion:

    number row1 row2 row3
    ----------------------
    1 1 0 0
    9 0 1 0
    10 1 0 0
    20 1 0 0
    21 0 0 1
    26 0 1 0
    60 2 3 0
    63 0 0 1
    72 0 1 0
    73 2 3 0
    74 1 3 1
    77 3 0 0
    83 1 0 0

    Is it possible to do it in a single query ? How can I have my query to
    get the distinct value from 3 columns at a time?

    I tried this but get way to much combinations (I get 3 three columns
    instead of only one with 1-9-10-20-21-26-60-63-72-73-74-77-83)

    SELECT DISTINCT row1, row2, row3 FROM theTable

    And How can I iterate threw the distinct numbers (if I get them right)?

    I'm using SQL Server 2000.

    Again thx for precious help.

    John


    *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    Don't just participate in USENET...get rewarded for it!
    John Grenier Guest

  3. #3

    Default Re: Count problem

    Try:

    SELECT sum(case row1 when 60 then 1 else 0 end ) as col1result ,
    sum(case row2 when 60 then 1 else 0 end ) as col2result ,
    sum(case row3 when 60 then 1 else 0 end ) as col3result
    FROM theTable

    --
    -Vishal
    Jonathan Grenier <jgrenier95hotmail.com> wrote in message
    news:ccd16ecb.0307112012.4d0dc11fposting.google.c om...
    > Hi,
    >
    > I have a problem counting the occurence of a number within 3 spefic
    > columns for one table.
    >
    > What I want to do is to count the number of occurence of a number (60
    > for example) for each of the three columns starting by row*. I'm able
    > to count it seperatly but can't find the correct way to do it for the
    > three in a single query. Anyone can help me out?
    >
    > tid gid row1 row2 row3
    > 1 1 20 72 0
    > 2 1 60 0 0
    > 3 1 77 73 0
    > 4 1 60 0 0
    > 5 1 77 60 74
    > 6 1 74 60 21
    > 7 1 77 60 73
    > 8 2 83 9 63
    > 9 2 1 26 0
    > 10 3 10 0 0
    >
    >
    > SELECT COUNT(*) as col1result FROM theTable
    > WHERE row1=60
    >
    > ANS: 2
    >
    > SELECT COUNT(*) as col2result FROM theTable
    > WHERE row2=60
    >
    > ANS: 3
    >
    > SELECT COUNT(*) as col3result FROM theTable
    > WHERE row3=60
    >
    > ANS: 0
    >
    > I'd like to have
    >
    > col1result col2result col3result
    > ----------------------------------------
    > 2 3 0
    >
    > In a single query.
    >
    > Thanks in advance.
    > John

    Vishal Parkar Guest

  4. #4

    Default Re: Count problem

    If you just want the answer, this is ugly because I do not know how to join
    the subqueries, although I have seen it done.

    /* The technique is to create a union of the individual row queries
    then compute the sum of the values */
    select row1 as value, count(*) as good from dataTable
    where row1 in
    (select distinct row1 from dataTable
    where row1 <> 0)
    group by row1
    union
    select row2 as value, count(*) as good from dataTable
    where row2 in
    (select distinct row2 from dataTable
    where row2 <> 0)

    group by row2

    union
    select row3 as value, count(*) as good from dataTable
    where row3 in
    (select distinct row3 from dataTable
    where row3 <> 0)
    group by row3
    order by value compute sum(count(*)) by value


    It gives you the sum, at least you can check your work.

    "John Grenier" <jgrenier95hotmail.com> wrote in message
    news:uGednaHSDHA.1624tk2msftngp13.phx.gbl...
    > Thanks Vishal.
    >
    > It works fine!
    >
    > Now I have another problem. I'd like to count the occurence of all the
    > distinct numbers present in the three columns (except 0 which is in fact
    > a dummy) and show the results in that fashion:
    >
    > number row1 row2 row3
    > ----------------------
    > 1 1 0 0
    > 9 0 1 0
    > 10 1 0 0
    > 20 1 0 0
    > 21 0 0 1
    > 26 0 1 0
    > 60 2 3 0
    > 63 0 0 1
    > 72 0 1 0
    > 73 2 3 0
    > 74 1 3 1
    > 77 3 0 0
    > 83 1 0 0
    >
    > Is it possible to do it in a single query ? How can I have my query to
    > get the distinct value from 3 columns at a time?
    >
    > I tried this but get way to much combinations (I get 3 three columns
    > instead of only one with 1-9-10-20-21-26-60-63-72-73-74-77-83)
    >
    > SELECT DISTINCT row1, row2, row3 FROM theTable
    >
    > And How can I iterate threw the distinct numbers (if I get them right)?
    >
    > I'm using SQL Server 2000.
    >
    > Again thx for precious help.
    >
    > John
    >
    >
    > *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    > Don't just participate in USENET...get rewarded for it!

    Jay Schmitendorf Guest

  5. #5

    Default Re: Count problem

    I had to use an alias for the subquery in order for it to work

    select ... from ( subquery as ti) group by value

    I also used union all

    select distinct value, sum(good) from
    ( select row1 as value, count(*) as good from dataTable
    where row1 in
    (select distinct row1 from dataTable
    where row1 <> 0)
    group by row1
    union all
    select row2 as value, count(*) as good from dataTable
    where row2 in
    (select distinct row2 from dataTable
    where row2 <> 0)

    group by row2

    union all
    select row3 as value, count(*) as good from dataTable
    where row3 in
    (select distinct row3 from dataTable
    where row3 <> 0)
    group by row3
    ) as sa
    group by value


    "John Grenier" <jgrenier95hotmail.com> wrote in message
    news:uGednaHSDHA.1624tk2msftngp13.phx.gbl...
    > Thanks Vishal.
    >
    > It works fine!
    >
    > Now I have another problem. I'd like to count the occurence of all the
    > distinct numbers present in the three columns (except 0 which is in fact
    > a dummy) and show the results in that fashion:
    >
    > number row1 row2 row3
    > ----------------------
    > 1 1 0 0
    > 9 0 1 0
    > 10 1 0 0
    > 20 1 0 0
    > 21 0 0 1
    > 26 0 1 0
    > 60 2 3 0
    > 63 0 0 1
    > 72 0 1 0
    > 73 2 3 0
    > 74 1 3 1
    > 77 3 0 0
    > 83 1 0 0
    >
    > Is it possible to do it in a single query ? How can I have my query to
    > get the distinct value from 3 columns at a time?
    >
    > I tried this but get way to much combinations (I get 3 three columns
    > instead of only one with 1-9-10-20-21-26-60-63-72-73-74-77-83)
    >
    > SELECT DISTINCT row1, row2, row3 FROM theTable
    >
    > And How can I iterate threw the distinct numbers (if I get them right)?
    >
    > I'm using SQL Server 2000.
    >
    > Again thx for precious help.
    >
    > John
    >
    >
    > *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    > Don't just participate in USENET...get rewarded for it!

    Jay Schmitendorf Guest

  6. #6

    Default count problem

    I'm working with the followin query, which works pretty well and
    retrieves 5 records:

    SELECT actores.id_actor_actores, peliculasactores.fk_id_actor_pelact,
    dvds.id_dvd_dvds, dvds.titulo_dvds
    FROM actores
    INNER JOIN peliculasactores ON
    actores.id_actor_actores=peliculasactores.fk_id_ac tor_pelact
    INNER JOIN dvds ON
    peliculasactores.fk_id_pelicula_pelact=dvds.id_dvd _dvds
    WHERE actores.id_actor_actores=1001
    ORDER BY dvds.fecha_ingreso_dvds DESC

    Now, I need to be able to previously count the result set, but i'm
    having trouble with "count" function

    SELECT count(*)
    FROM actores
    INNER JOIN peliculasactores ON
    actores.id_actor_actores=peliculasactores.fk_id_ac tor_pelact
    INNER JOIN dvds ON
    peliculasactores.fk_id_pelicula_pelact=dvds.id_dvd _dvds
    WHERE actores.id_actor_actores=1001
    ORDER BY dvds.fecha_ingreso_dvds DESC

    Error Code : 1064
    You have an error in your SQL syntax; check the manual that corresponds
    to your MySQL server version for the right syntax to use near 'SELECT
    count (*)

    It seems that the problem has to do with the INNER JOIN, beacause i've
    used count(*) before (without inner joins) and it worked. Can anyone
    give me a hint?

    regards

    julian_m Guest

  7. #7

    Default Re: count problem

    julian_m wrote:
    > I'm working with the followin query, which works pretty well and
    > retrieves 5 records:
    >
    > SELECT actores.id_actor_actores, peliculasactores.fk_id_actor_pelact,
    > dvds.id_dvd_dvds, dvds.titulo_dvds
    > FROM actores
    > INNER JOIN peliculasactores ON
    > actores.id_actor_actores=peliculasactores.fk_id_ac tor_pelact
    > INNER JOIN dvds ON
    > peliculasactores.fk_id_pelicula_pelact=dvds.id_dvd _dvds
    > WHERE actores.id_actor_actores=1001
    > ORDER BY dvds.fecha_ingreso_dvds DESC
    >
    > Now, I need to be able to previously count the result set, but i'm
    > having trouble with "count" function
    >
    > SELECT count(*)
    > FROM actores
    > INNER JOIN peliculasactores ON
    > actores.id_actor_actores=peliculasactores.fk_id_ac tor_pelact
    > INNER JOIN dvds ON
    > peliculasactores.fk_id_pelicula_pelact=dvds.id_dvd _dvds
    > WHERE actores.id_actor_actores=1001
    > ORDER BY dvds.fecha_ingreso_dvds DESC
    >
    > Error Code : 1064
    > You have an error in your SQL syntax; check the manual that corresponds
    > to your MySQL server version for the right syntax to use near 'SELECT
    > count (*)
    >
    > It seems that the problem has to do with the INNER JOIN, beacause i've
    > used count(*) before (without inner joins) and it worked. Can anyone
    > give me a hint?
    >
    > regards
    >
    According to the error message, you used "count (*)" instead
    of "count(*)" (notice the space).

    ciao
    gmax

    --
    _ _ _ _
    (_|| | |(_|><
    _|
    [url]http://gmax.oltrelinux.com[/url]
    Giuseppe Maxia Guest

Similar Threads

  1. Count Record Problem
    By patelajk in forum Dreamweaver AppDev
    Replies: 0
    Last Post: April 17th, 03:28 PM
  2. small count problem
    By Lasse Edsvik in forum ASP Database
    Replies: 12
    Last Post: January 7th, 07:50 PM
  3. [PHP] COUNT(*)
    By Richard Baskett in forum PHP Development
    Replies: 0
    Last Post: August 1st, 08:39 AM
  4. COUNT(*)
    By Craig Roberts in forum PHP Development
    Replies: 0
    Last Post: August 1st, 08:39 AM
  5. count
    By dennis in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: July 7th, 07:26 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139