total and group by

• coool_sweet

  SSCommitted

  Points: 1892

  More actions

  November 16, 2016 at 11:39 am

  #315748

  i have table like this

  id category price

  1 pen 32.45

  1 pencil 34

  3 tea 21

  2 tea 32.43

  3 pen 32

  2 pencil 32

  1 pen 12

  1 pencil 11

  3 tea 10

  what i need o do is total(pen)/total(pencil) for given product and then i need to join this with another table?

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  November 16, 2016 at 2:00 pm

  #1912916

  You can use something like this

  WITH CTE AS(

  SELECT Product,

  SUM( CASE WHEN Category = 'Something' THEN Value ELSE 0 END) AS Total1,

  SUM( CASE WHEN Category = 'Something Else' THEN Value ELSE 0 END) AS Total2

  FROM YourTable

  WHERE Category IN('Something', 'Something Else')

  GROUP BY Product

  )

  SELECT *

  FROM CTE;

  That's just the basic structure, which you have to understand to be able to complete your requirements.

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2