Count the amount of times values appear in the database

• paul 69259

  Hall of Fame

  Points: 3068

  More actions

  August 8, 2018 at 8:28 am

  #407660

  Hello,

  I have a table of "jobs" which I need to export to an online accounting software.

  What I need to do is count the quantity of jobs that have a matching service and vehicle and total the subtotal, tax amount and total for that group of data.

  This is an example table.......

  
create table jobs
(jobnumber int,
service char(10),
vehicle Char(20),
SubTotal Decimal(7,2),
TaxAmount Decimal (7,2),
Total Decimal (7,2))


  Here is some sample data to insert.....

  
insert into jobs (jobnumber,service,vehicle,subtotal,TaxAmount,total)
values
(11124555,'ND16','OVERNIGHT',10.00,2.50,12.50)
insert into jobs (jobnumber,service,vehicle,subtotal,TaxAmount,total)
values
(11124293,'ND16','OVERNIGHT',20.00,5.00,25.00)
insert into jobs (jobnumber,service,vehicle,subtotal,TaxAmount,total)
values
(11124770,'LSD','LONDON SAMEDAY',10.00,2.50,12.50)
insert into jobs (jobnumber,service,vehicle,subtotal,TaxAmount,total)
values
(11124013,'NDTL','ND TAIL LIFT',15.00,5.00,20.00)
insert into jobs (jobnumber,service,vehicle,subtotal,TaxAmount,total)
values
(11124054,'ND16','OVERNIGHT',20.00,5.00,25.00)
insert into jobs (jobnumber,service,vehicle,subtotal,TaxAmount,total)
values
(11124411,'LSD','LONDON SAMEDAY',10.00,2.50,12.50)
insert into jobs (jobnumber,service,vehicle,subtotal,TaxAmount,total)
values
(11124427,'NDTL','ND TAIL LIFT',15.00,5.00,20.00)
insert into jobs (jobnumber,service,vehicle,subtotal,TaxAmount,total)
values
(11124689,'ND16','OVERNIGHT',10.00,2.50,12.50)
insert into jobs (jobnumber,service,vehicle,subtotal,TaxAmount,total)
values
(11124689,'ND16','NEXTDAY',10.00,2.50,12.50)
insert into jobs (jobnumber,service,vehicle,subtotal,TaxAmount,total)
values
(11124649,'ND16','OVERNIGHT',10.00,2.50,12.50)


  So, from the data the results I'd be looking to obtain would be

  Description - NDTL NDTAIL LIFT  quantity - 1 Sub total - 15.00 Tax - 5.00 Total 20.00
  Description - ND16 OVERNIGHT Qunatity - 4 Sub total -  60.00 Tax - 15 Total 75.00
  Description - LSD london sameday Qunatity 2 subtotal - 20.00 Tax 5.00 Total 25.00

  Any idea how I can do this please?

  Thanks in advance
  Paul.

• below86

  SSChampion

  Points: 11532

  More actions

  August 8, 2018 at 9:00 am

  #2000834

  I created it as a temp table, but is this what you want?
  SELECT [service], vehicle,
    COUNT(*) AS Quantity,
    SUM(subtotal) AS subtotal,
    SUM(TaxAmount) AS TaxAmount,
    SUM(total) AS Total
FROM #jobs
GROUP BY [service], vehicle
;
  

  -------------------------------------------------------------
  we travel not to escape life but for life not to escape us
  Don't fear failure, fear regret.

• paul 69259

  Hall of Fame

  Points: 3068

  More actions

  August 8, 2018 at 9:09 am

  #2000840

  Hello,

  Thank you for you response. 

  It's close but the issue is it counts the job with ND16 service and NEXTDAY vehicle in with the rest of the ND16 services.

  There could be a load of services with the same code but they could be on a different vehicle, so they would need to be counted separately. The same goes for Vehicles, there could be a load of vehicles that have the same name but they may have different services.

  Thank you
  Paul.

• paul 69259

  Hall of Fame

  Points: 3068

  More actions

  August 8, 2018 at 9:13 am

  #2000845

  Oh, i've just realised all I have to do is put the '[' brackets around the vehicle as well as the service. Brilliant.

  Thank you very much

• paul 69259

  Hall of Fame

  Points: 3068

  More actions

  August 8, 2018 at 9:19 am

  #2000848

  Hmmm, it doesn't seem to like me using the square brackets when I'm doing a join. It says invalid column name

  
SELECT
[S.SERVICE],[V.VEHICLE],
count(*) as Quantity,
concat(J.JOBNUMBER,' ',V.VEHICLE,' ',S.SERVICE) as Description,
sum(isnull(J.PRICE,0)) as Price,
sum(isnull(J.VAT,0)) as VAT,
sum(ISNULL((J.PRICE + j.VAT),0)) AS Total
FROM dbo.Invoice I
INNER JOIN dbo.Jobs J
ON I.INVOICEID = J.INVOICEID
INNER JOIN dbo.Vehicle V
ON J.VEHICLEID = V.VEHICLEID
INNER JOIN
dbo.Service S
ON J.SERVICEID = S.SERVICEID
WHERE I.invoicenumber = 341    
group by J.JOBNUMBER, V.VEHICLE, S.SERVICE, I.INVOICENUMBER,J.Price,J.VAT


• below86

  SSChampion

  Points: 11532

  More actions

  August 8, 2018 at 9:33 am

  #2000855

  paul 69259 - Wednesday, August 8, 2018 9:19 AM

  Hmmm, it doesn't seem to like me using the square brackets when I'm doing a join. It says invalid column name

  
SELECT
[S.SERVICE],[V.VEHICLE],
count(*) as Quantity,
concat(J.JOBNUMBER,' ',V.VEHICLE,' ',S.SERVICE) as Description,
sum(isnull(J.PRICE,0)) as Price,
sum(isnull(J.VAT,0)) as VAT,
sum(ISNULL((J.PRICE + j.VAT),0)) AS Total
FROM dbo.Invoice I
INNER JOIN dbo.Jobs J
ON I.INVOICEID = J.INVOICEID
INNER JOIN dbo.Vehicle V
ON J.VEHICLEID = V.VEHICLEID
INNER JOIN
dbo.Service S
ON J.SERVICEID = S.SERVICEID
WHERE I.invoicenumber = 341    
group by J.JOBNUMBER, V.VEHICLE, S.SERVICE, I.INVOICENUMBER,J.Price,J.VAT


  Change the values in the SELECT the same as the GROUP BY.  Like S.[service], and V.vehicle

  -------------------------------------------------------------
  we travel not to escape life but for life not to escape us
  Don't fear failure, fear regret.

• below86

  SSChampion

  Points: 11532

  More actions

  August 8, 2018 at 9:35 am

  #2000856

  And you really don't want a table called service and a field called service.  This will be very confusing.

  -------------------------------------------------------------
  we travel not to escape life but for life not to escape us
  Don't fear failure, fear regret.

• paul 69259

  Hall of Fame

  Points: 3068

  More actions

  August 8, 2018 at 9:40 am

  #2000859

  Brilliant, thank you very much for you help.

• drew.allen

  SSC Guru

  Points: 76985

  More actions

  August 8, 2018 at 2:44 pm

  #2000914

  paul 69259 - Wednesday, August 8, 2018 9:19 AM

  Hmmm, it doesn't seem to like me using the square brackets when I'm doing a join. It says invalid column name

  
SELECT
[S.SERVICE],[V.VEHICLE],
count(*) as Quantity,
concat(J.JOBNUMBER,' ',V.VEHICLE,' ',S.SERVICE) as Description,
sum(isnull(J.PRICE,0)) as Price,
sum(isnull(J.VAT,0)) as VAT,
sum(ISNULL((J.PRICE + j.VAT),0)) AS Total
FROM dbo.Invoice I
INNER JOIN dbo.Jobs J
ON I.INVOICEID = J.INVOICEID
INNER JOIN dbo.Vehicle V
ON J.VEHICLEID = V.VEHICLEID
INNER JOIN
dbo.Service S
ON J.SERVICEID = S.SERVICEID
WHERE I.invoicenumber = 341    
group by J.JOBNUMBER, V.VEHICLE, S.SERVICE, I.INVOICENUMBER,J.Price,J.VAT


  When using brackets, each part is quoted separately. So, is a field name with no table name/alias specified whereas .[SERVICE] is a table name/alias and a field name.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• paul 69259

  Hall of Fame

  Points: 3068

  More actions

  August 9, 2018 at 1:56 am

  #2000953

  Ok, thank you for the information Drew.

• paul 69259

  Hall of Fame

  Points: 3068

  More actions

  August 13, 2018 at 6:46 am

  #2001454

  Is there any way I can reference "Quantity" so that I can divide the "Price" by the "Quantity" to get the price per unit?

• below86

  SSChampion

  Points: 11532

  More actions

  August 13, 2018 at 7:24 am

  #2001468

  paul 69259 - Monday, August 13, 2018 6:46 AM

  Is there any way I can reference "Quantity" so that I can divide the "Price" by the "Quantity" to get the price per unit?

  You would want to use a CTE so you could reference it to calculate the price per unit.

  -------------------------------------------------------------
  we travel not to escape life but for life not to escape us
  Don't fear failure, fear regret.

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  August 13, 2018 at 2:32 pm

  #2001556

  paul 69259 - Monday, August 13, 2018 6:46 AM

  Is there any way I can reference "Quantity" so that I can divide the "Price" by the "Quantity" to get the price per unit?

  That depends almost entirely on the nature of your query.  If you can post it, we have a much better shot at a good answer, because otherwise we have to guess.

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• ben.brugman

  SSChampion

  Points: 13369

  More actions

  August 14, 2018 at 7:55 am

  #2001646

  Something around these lines, especially the second solution might help you on.
  The second query is equivalent to the first query, but might help in 'chopping' up the query.

  Ben
  
SELECT [service], [vehicle],
  COUNT(*) AS Quantity,
  SUM(subtotal) AS subtotal,
  SUM(TaxAmount) AS TaxAmount,
  SUM(total) AS Total,
  SUM(total)*1.0 / COUNT(*) AS Price_Per_Unit
FROM jobs
GROUP BY [service], [vehicle]
;

  -- Or similar (same result) :
;WITH
A as (SELECT [service], vehicle,
            COUNT(*) AS Quantity,
            SUM(subtotal) AS subtotal,
            SUM(TaxAmount) AS TaxAmount,
            SUM(total) AS Total
     FROM jobs
     GROUP BY [service], [vehicle])
, B AS (SELECT *, 1.0*total/quantity Price_Per_Unit FROM A)
SELECT * FROM b    

• paul 69259

  Hall of Fame

  Points: 3068

  More actions

  August 14, 2018 at 8:37 am

  #2001658

  Brilliant Ben, thats done the trick. Thank you.

  I don't understand how this line works though....
  
SUM(total)*1.0 / COUNT(*) AS Price_Per_Unit


  How does it know that count(*) is the quantity?

  Thank you
  Paul.