SQL Query

• river1

  SSC-Insane

  Points: 24309

  More actions

  February 25, 2015 at 4:59 am

  #315679

  Hi,

  I have a table with the following columns:

  cod_rep_fiscal, cod_met_tributario, ctb, status

  Note - ctb is the primary key.

  Sample of the data

  1.01; tp1; ola, A

  1.01; tp1; oli, A

  1.01; tp5; ole, A

  1.01; tp2; olo, I

  1.01; tp1; olu, A

  1.02; tp7; novo1, A

  1.02; tp1; novo2, C

  1.02; tp5; novo3, A

  1.02; tp1; novo4, C

  I want to return this:

  cod_rep_fiscal; tp1;tp2;tp5;tp7; total

  Î want to get (group by cod-rep_fiscal) the number of ctb per cod-met_tributario where the status = 'A'. Also want a total

  Example for this data:

  1.01; tp1; ola, A

  1.01; tp1; oli, A

  1.01; tp5; ole, A

  1.01; tp2; olo, I

  1.01; tp1; olu, A

  cod_rep_fiscal; tp1;tp2;tp5;tp7; total

  1.01; 3; 0;1; 0; 4

  This should be ordered by total.

  Can someone help?

  All the data (columns) are varchar

• Mark Cowne

  One Orange Chip

  Points: 26954

  More actions

  February 25, 2015 at 5:16 am

  #1779852

  Something like this?

  SELECT cod_rep_fiscal,

  COUNT(CASE WHEN cod_met_tributario = 'tp1' THEN cod_met_tributario END) AS tp1,

  COUNT(CASE WHEN cod_met_tributario = 'tp2' THEN cod_met_tributario END) AS tp2,

  COUNT(CASE WHEN cod_met_tributario = 'tp5' THEN cod_met_tributario END) AS tp5,

  COUNT(CASE WHEN cod_met_tributario = 'tp7' THEN cod_met_tributario END) AS tp7,

  COUNT(cod_met_tributario) AS total

  FROM mytable

  WHERE status = 'A'

  GROUP BY cod_rep_fiscal

  ORDER BY total;

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• Pulivarthi Sasidhar

  SSCertifiable

  Points: 6706

  More actions

  February 25, 2015 at 5:19 am

  #1779855

  Use coalesce for (after applying SUM() and Groupy by) as A