title: Use COALESCE with Group by
body: <p>I have a single table which contains data like the following</p>
  
  <pre><code>pid     category name           subcategoryname
  --------------------------------------------------------
  424 Bathing &amp; Infant Care   Baby Lotion
  428 Bathing &amp; Infant Care   Baby Lotion
  445 Bathing &amp; Infant Care   Bath Accessories
  337 Bathing &amp; Infant Care   Bath Accessories
  69  Bathing &amp; Infant Care   Bath Accessories
  143 Bathing &amp; Infant Care   Bath Accessories
  147 Bathing &amp; Infant Care   Bath Accessories
  213 Bathing &amp; Infant Care   Bath Accessories
  270 Bathing &amp; Infant Care   Bath Accessories
  197 Bathing &amp; Infant Care   Bath Toys
  390 Bathing &amp; Infant Care   Bath Toys
  396 Bathing &amp; Infant Care   Bath Toys
  416 Bathing &amp; Infant Care   Bath Toys
  361 Bathing &amp; Infant Care   Bath Tub
  447 Bathing &amp; Infant Care   Bath Tub
  464 Bathing &amp; Infant Care   Bath Tub
  491 Bathing &amp; Infant Care   Bath Tub
  204 Bathing &amp; Infant Care   Bath Tub
  286 Bathing &amp; Infant Care   Bath Tub
  237 Bathing &amp; Infant Care   Bath Tub
  240 Bathing &amp; Infant Care   Bath Tub
  248 Bathing &amp; Infant Care   Bath Tub
  148 Bathing &amp; Infant Care   Bath Tub
  23  Bathing &amp; Infant Care   Bath Tub
  153 Bathing &amp; Infant Care   Humidifier
  485 Bathing &amp; Infant Care   Humidifier
  14  Bathing &amp; Infant Care   Other
  64  Bathing &amp; Infant Care   Other
  73  Bathing &amp; Infant Care   Shampoo &amp; Body Wash
  449 Bathing &amp; Infant Care   Towels
  </code></pre>
  
  <p>The desired result is i need to write a sql script where i pass category name and output should be</p>
  
  <pre><code>subcategoryname | total count of records | comma separated list of pid
  </code></pre>
  
  <p>so if i pass "Bathing &amp; Infant care" to my SQL, it should give me</p>
  
  <pre><code>subcategoryname totalcount pid
  --------------------------------
  Baby Lotion     2          424,428
  Other           2          14,64
  Baby Accesso    7          445,337,69,143,147,213,270
  </code></pre>