Pastebin launched a little side project called HostCabi.net, check it out ;-)Don't like ads? PRO users don't see any ads ;-)
Guest

SQL query

By: a guest on Aug 24th, 2012  |  syntax: None  |  size: 3.22 KB  |  hits: 20  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  1. No group by applied -
  2. select id_seed, id_campaign, id_chat from messages where id_campaign = 140;
  3. +------------+-------------+----------------------------------------+
  4. | id_seed    | id_campaign | id_chat                                |
  5. +------------+-------------+----------------------------------------+
  6. | 4102593936 |         140 | a6ab87684aa1a90dff7ea61cf618d3f9CCC140 |
  7. | 4102593936 |         140 | 0d60b2a4a79938b07ac39b977a96efc4CCC140 |
  8. | 4102593936 |         140 | d5b6712b51878a2f37e7004bc5b57171CCC140 |
  9. | 4102593936 |         140 | d2f5fc4c1d7d9d95cfd4846234f6aad3CCC140 |
  10. | 4102593936 |         140 | 1118cb4436f5e5d03f4805d0b9e8bf3eCCC140 |
  11. | 4075174669 |         140 | 12d59be001fc6d795c2da5f9456b41afCCC140 |
  12. | 4075174669 |         140 | d09d4cf87e5d8b2c3efcfea1fd94f9eaCCC140 |
  13. | 4075174669 |         140 | d09d4cf87e5d8b2c3efcfea1fd94f9eaCCC140 |
  14. | 4075174669 |         140 | ace48ab48c4409e6307a2dc55856ce72CCC140 |
  15. | 4102593936 |         140 | 2032e80ca46bca4a5d97f2da92e9f7b1CCC140 |
  16. | 4102593936 |         140 | 2032e80ca46bca4a5d97f2da92e9f7b1CCC140 |
  17. | 4102593936 |         140 | 2032e80ca46bca4a5d97f2da92e9f7b1CCC140 |
  18. | 4102593936 |         140 | 2032e80ca46bca4a5d97f2da92e9f7b1CCC140 |
  19. | 4102593936 |         140 | 2032e80ca46bca4a5d97f2da92e9f7b1CCC140 |
  20. +------------+-------------+----------------------------------------+
  21.  
  22. after group by applied -
  23. select id_seed, id_campaign, id_chat from messages where id_campaign = 140 group by id_chat;
  24. +------------+-------------+----------------------------------------+
  25. | id_seed    | id_campaign | id_chat                                |
  26. +------------+-------------+----------------------------------------+
  27. | 4102593936 |         140 | 0d60b2a4a79938b07ac39b977a96efc4CCC140 |
  28. | 4102593936 |         140 | 1118cb4436f5e5d03f4805d0b9e8bf3eCCC140 |
  29. | 4075174669 |         140 | 12d59be001fc6d795c2da5f9456b41afCCC140 |
  30. | 4102593936 |         140 | 2032e80ca46bca4a5d97f2da92e9f7b1CCC140 |
  31. | 4102593936 |         140 | a6ab87684aa1a90dff7ea61cf618d3f9CCC140 |
  32. | 4075174669 |         140 | ace48ab48c4409e6307a2dc55856ce72CCC140 |
  33. | 4075174669 |         140 | d09d4cf87e5d8b2c3efcfea1fd94f9eaCCC140 |
  34. | 4102593936 |         140 | d2f5fc4c1d7d9d95cfd4846234f6aad3CCC140 |
  35. | 4102593936 |         140 | d5b6712b51878a2f37e7004bc5b57171CCC140 |
  36. +------------+-------------+----------------------------------------+
  37.  
  38. peter's example here, which gives total count -
  39. elect count(*) as c, id_seed from messages where id_campaign = 140 group by id_seed;
  40. +----+------------+
  41. | c  | id_seed    |
  42. +----+------------+
  43. |  4 | 4075174669 |
  44. | 10 | 4102593936 |
  45. +----+------------+
  46.  
  47. my shot at it where i'm trying to count the records that show up after the group by is applied. note how the same values show up twice instead of adding together...?? -
  48. select distinct count(id_seed) as c, id_seed from messages where id_campaign = 140 group by id_chat, id_seed;
  49. +---+------------+
  50. | c | id_seed    |
  51. +---+------------+
  52. | 1 | 4075174669 |
  53. | 5 | 4102593936 |
  54. | 1 | 4102593936 |
  55. | 2 | 4075174669 |
  56. +---+------------+
  57.  
  58. here's the final result i'm trying to get -
  59. +---+------------+
  60. | c | id_seed    |
  61. +---+------------+
  62. | 3 | 4075174669 |
  63. | 6 | 4102593936 |
  64. +---+------------+