#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    10
    Rep Power
    0

    Question Count how many values of the same type according to certain ID


    Hi, i've a situation like this:


    PHP Code:
    idMessage idUser  Motivazione 
         65      43           2     
         45      43           3     
         65      43           1     
         55      43           1 

    I've to select all the records and calculate how many a certain number in "Motivazione" appears for the same idMessage.

    Assuming that values in Motivazione can go from 1 to 3, according to this image the result should be:

    65 -> [1*1] [2*1] //in fact 1 and 2 appears once
    45 -> [3*1]
    55 -> [1*1]


    if 65 appears for 20 times with Motivazione = 3, the result would look:

    65 -> [3*20]

    Is it possible or i've to perform 3 distinct queries?
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    It's called grouping.

    Group by idMessage and Motivazione and then do a COUNT(*).
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    10
    Rep Power
    0
    Yes i know grouping, but is it possibile to apply two grouping in the same query?

    Actually my query is
    SELECT *
    FROM reports
    GROUP BY idMessage

    Where i've to place Group by Motivazione?

    EDIT:
    i now tryied this

    SELECT idMessage, Motivazione COUNT(*)
    FROM reports
    GROUP BY idMessage, Motivazione;

    The result is

    Array (
    [0] => Array ( [idMessage] => 45 [0] => 45 [Motivazione] => 3 [1] => 3 [COUNT(*)] => 1 [2] => 1 )
    //other records
    [2] => Array ( [idAnnuncio] => 65 [0] => 65 [Motivazione] => 1 [1] => 1 [COUNT(*)] => 2 [2] => 2 ) //two records with motivatione= 1 and id=65
    [3] => Array ( [idMessage] => 65 [0] => 65 [Motivazione] => 2 [1] => 2 [COUNT(*)] => 1 [2] => 1 ) ) //one record with motivatione= 2 and id=65
    )

    This is the best i can do ?

    I would want a final structure like this

    PHP Code:
    ID 3|
     
    65   1    2   0
     45   1    0   0
     55   0    1   0 
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    the simple grouping query is, yes, the best you can do with sql

    the layout you are looking for is generally called a crosstab and there are apparently easy ways to produce it in php

    would you like this thread to be moved to the php forum so you can learn how to do it?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Originally Posted by American horizo
    I would want a final structure like this
    Then create this table in your application.

    SQL is not Excel. It's for storing and processing data, not printing pretty tables. If you want a special formatting or colorful tables or whatever, do that in your application.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    10
    Rep Power
    0

    Exclamation


    Originally Posted by r937
    the simple grouping query is, yes, the best you can do with sql

    the layout you are looking for is generally called a crosstab and there are apparently easy ways to produce it in php

    would you like this thread to be moved to the php forum so you can learn how to do it?
    Maybe i' have not explained well what i want. What i mean is that, once launched the fechAll() method, i've a multidimensional array like this

    Array(
    Array([idMessage]=>65, [1]=>1, [2]=>2, [3]=>0)
    Array([idMessage]=>45, [1]=>1, [2]=>0, [3]=>0)
    and so on..
    )

    After that i know that for a echo and stylize the result i need to echo with PHP and use HTML format (is that the crosstab that you're refering?)


    But actually the query posted above give me as result
    Array (
    [0] => Array ( [idAnnuncio] => 45 [0] => 45 [Motivazione] => 3 [1] => 3 [COUNT(*)] => 1 [2] => 1 )
    [1] => Array ( [idAnnuncio] => 55 [0] => 55 [Motivazione] => 1 [1] => 1 [COUNT(*)] => 1 [2] => 1 )
    [2] => Array ( [idAnnuncio] => 65 [0] => 65 [Motivazione] => 1 [1] => 1 [COUNT(*)] => 2 [2] => 2 )
    [3] => Array ( [idAnnuncio] => 65 [0] => 65 [Motivazione] => 2 [1] => 2 [COUNT(*)] => 1 [2] => 1 )
    )

    This assume that i've to perform some sort of other operation in PHP for build the desired structure.

    EDIT: maybe i've found something about crosstab i need
    I can't post links, anyway the query is
    SELECT DATE_FORMAT(`invoice`.`date`, '%M') AS `month`, COUNT(DISTINCT `invoice`.`id`) AS `product_count`, COUNT(*) AS `invoice_count`, SUM(`invoice_product`.`amount`) AS `turnover`,
    SUM(`product`.`category_id`=1) AS `hardware_count`, SUM(IF(`product`.`category_id`=1, `invoice_product`.`amount`, 0)) AS `hardware_turnover`,
    SUM(`product`.`category_id`=2) AS `software_count`, SUM(IF(`product`.`category_id`=2, `invoice_product`.`amount`, 0)) AS `software_turnover`,
    SUM(`product`.`category_id`=3) AS `cables_count`, SUM(IF(`product`.`category_id`=3, `invoice_product`.`amount`, 0)) AS `cables_turnover`,
    SUM(`product`.`category_id`=4) AS `other_count`, SUM(IF(`product`.`category_id`=4, `invoice_product`.`amount`, 0)) AS `other_turnover`
    FROM `invoice` INNER JOIN `invoice_product` ON `invoice`.`id` = `invoice_product`.`invoice_id` LEFT JOIN `product` ON `invoice_product`.`product_id` = `product`.`id` LEFT JOIN `category` ON `product`.`category_id` = `category`.`id`
    WHERE `date` BETWEEN '2008-01-01' AND '2008-12-31'
    GROUP BY MONTH(`invoice`.`date`), `category`.`id`
    I'm confused... What are that quotes beyond the column names? And the dot between column names?
  12. #7
  13. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Forget the query. As we already said, this is a PHP task. Yes, you have to build the structure in PHP. No, you don't do it with MySQL. That's simply not what a database system is for.

    If you have basic programming knowledge, this shouldn't be too hard. It's only a few lines of PHP. So do you want us to help you?
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    10
    Rep Power
    0
    Originally Posted by Jacques1
    Forget the query. As we already said, this is a PHP task. Yes, you have to build the structure in PHP. No, you don't do it with MySQL. That's simply not what a database system is for.

    If you have basic programming knowledge, this shouldn't be too hard. It's only a few lines of PHP. So do you want us to help you?
    yes i know how to do that in PHP, but the risk is that with a huge piece of data, the "conversion" process could be very slow

    Anyway what is this crosstab method mentioned by r937 and for what reason in the query posted above the guy successful "grouped" and counted elements?
  16. #9
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    Originally Posted by American horizo
    yes i know how to do that in PHP, but the risk is that with a huge piece of data, the "conversion" process could be very slow
    with sql, it will be even slower

    your call
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    10
    Rep Power
    0
    Originally Posted by r937
    with sql, it will be even slower

    your call
    ok anyway i want to understand how i can use crosstab. In the link i found the guy has a table like this

    PHP Code:
    month     category   product_count  turnover   
    January   Hardware   62             4821.31   
    January   Software   51             4419.41   
    January   Cables     12             291.54   
    February  Hardware   71             8408.93   
    February  Software   101            11726.36   
    February  Cables     17             312.32   
    February  Other      2              410.00   
    March     Hardware   21             2371.58   
    March     Software   81             8238.81    
    March     Cables     13             312.32 
    and with this query
    SELECT DATE_FORMAT(`invoice`.`date`, '%M') AS `month`, COUNT(DISTINCT `invoice`.`id`) AS `product_count`, COUNT(*) AS `invoice_count`, SUM(`invoice_product`.`amount`) AS `turnover`,
    SUM(`product`.`category_id`=1) AS `hardware_count`, SUM(IF(`product`.`category_id`=1, `invoice_product`.`amount`, 0)) AS `hardware_turnover`,
    SUM(`product`.`category_id`=2) AS `software_count`, SUM(IF(`product`.`category_id`=2, `invoice_product`.`amount`, 0)) AS `software_turnover`,
    SUM(`product`.`category_id`=3) AS `cables_count`, SUM(IF(`product`.`category_id`=3, `invoice_product`.`amount`, 0)) AS `cables_turnover`,
    SUM(`product`.`category_id`=4) AS `other_count`, SUM(IF(`product`.`category_id`=4, `invoice_product`.`amount`, 0)) AS `other_turnover`
    FROM `invoice` INNER JOIN `invoice_product` ON `invoice`.`id` = `invoice_product`.`invoice_id` LEFT JOIN `product` ON `invoice_product`.`product_id` = `product`.`id` LEFT JOIN `category` ON `product`.`category_id` = `category`.`id`
    WHERE `date` BETWEEN '2008-01-01' AND '2008-12-31'
    GROUP BY MONTH(`invoice`.`date`), `category`.`id`


    he obtain

    PHP Code:
    month     invoice_count  turnover    hardware_count  hardware_turnover  software_count  software_turnover  cables_count  cables_turnover  other_count  other_turnover
    January   84             9532.26     62              4821.31            51              4419.41            12            291.54           0            0
    February  141            20857.61    71              8408.93            101             11726.36           17            312.32           2            410.00
    March     91             10922.71    21              2371.58            81              8238.81            13            312.32           0            0 
  20. #11
  21. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    yup, that's exactly how you do it

    have fun

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  22. #12
  23. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    10
    Rep Power
    0
    Originally Posted by r937
    yup, that's exactly how you do it

    have fun

    sorry, but i don't undesrtand.. In those string where i have used a PHP syntax?

    There?
    PHP Code:
    SUM(`product`.`category_id`=1
  24. #13
  25. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    Originally Posted by American horizo
    In those string where i have used a PHP syntax?
    that's pure mysql, no php
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  26. #14
  27. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    10
    Rep Power
    0
    you said

    the layout you are looking for is generally called a crosstab and there are apparently easy ways to produce it in php would you like this thread to be moved to the php forum so you can learn how to do it?
    so why he used pure mysql?
  28. #15
  29. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    Originally Posted by American horizo
    so why he used pure mysql?
    because he wanted a cumbersome, difficult to maintain, inelegant, and inefficient solution
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo