Select Result One Record Per Job 2

[codrutza]

I have the table Jobs:
JNo JDate Sales
10 21/01/2015 10
11 21/01/2015 20
12 22/01/2015 30
13 23/01/2015 10
14 26/02/2015 10
15 26/02/2015 40
16 26/02/2015 30
17 27/03/2015 20
18 28/03/2015 40
19 29/03/2015 30
20 29/03/2015 60
40 22/01/2016 0
41 23/01/2015 50
42 24/01/2016 50
43 24/01/2016 60
44 23/01/2016 20
45 26/02/2016 10
46 26/02/2016 40
47 26/02/2016 50
48 26/02/2016 20

And the table Cons:
CNo CJNo
131 11
141 12
161 13
169 12
191 16
201 10
202 10
203 10
204 10
205 10
209 16
211 20
241 14
301 16
401 40
409 41
441 45
442 45
443 45
447 45
509 16

With
SELECT Jobs.JNo, Jobs.JDate, Jobs.Sales, Cons.CNo, Cons.CJNo
FROM Jobs LEFT JOIN Cons ON Jobs.JNo = Cons.CJNo
ORDER BY Jobs.JNo;
I get
JNo JDate Sales CNo CJNo
10 21/01/2015 10 203 10
10 21/01/2015 10 204 10
10 21/01/2015 10 205 10
10 21/01/2015 10 201 10
10 21/01/2015 10 202 10
11 21/01/2015 20 131 11
12 22/01/2015 30 141 12
12 22/01/2015 30 169 12
13 23/01/2015 10 161 13
14 26/02/2015 10 241 14
15 26/02/2015 40
16 26/02/2015 30 191 16
16 26/02/2015 30 301 16
16 26/02/2015 30 209 16
16 26/02/2015 30 509 16
17 27/03/2015 20
18 28/03/2015 40
19 29/03/2015 30
20 29/03/2015 60 211 20
40 22/01/2016 0 401 40
41 23/01/2015 50 409 41
42 24/01/2016 50
43 24/01/2016 60
44 23/01/2016 20
45 26/02/2016 10 441 45
45 26/02/2016 10 443 45
45 26/02/2016 10 442 45
45 26/02/2016 10 447 45
46 26/02/2016 40
47 26/02/2016 50
48 26/02/2016 20

But I only want to get
JNo JDate Sales

​

TotalCNoperJob
10 21/01/2015 10 5
11 21/01/2015 20 1
12 22/01/2015 30 2
13 23/01/2015 10 1
14 26/02/2015 10 1
15 26/02/2015 40
16 26/02/2015 30 4
17 27/03/2015 20
18 28/03/2015 40
19 29/03/2015 30
20 29/03/2015 60 1
40 22/01/2016 0 1
41 23/01/2015 50 1
42 24/01/2016 50
43 24/01/2016 60
44 23/01/2016 20
45 26/02/2016 10 4
46 26/02/2016 40
47 26/02/2016 50
48 26/02/2016 20

Is there a way that I can write an embedded select for Cons or something?

 

[SkipVought]

SELECT Jobs.JNo, Jobs.JDate, Jobs.Sales, Count(Cons.CNo) AS TotalCNoperJob
FROM Jobs LEFT JOIN Cons ON Jobs.JNo = Cons.CJNo
Group By Jobs.JNo, Jobs.JDate, Jobs.Sales
ORDER BY Jobs.JNo;

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[codrutza]

Thank you, Skip. So what if I have to select many more fields in my select?
- Do I put them all in the group by; maybe it is silly, I'm afraid of group by all the fields; could have some influence in the result or not? I will have to add as well the rest of the fields by month, year, ...at the end.
or
- is it better with an embedded select for TotalCNoperJob? I don't know to write an embedded select for this case. Can you help?

 

[SkipVought]

Group By? YES, all other fields.

"...the rest of the fields by month, year..."

??? Plz explain.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Olaf Doschke]

Generally speaking, if you add more fields to the result, group by disallows this (it only allows aggregates of further fields, ie sum, min, max, etc) and to add the to the field list unmodified by aggregations, you have to add them to the group by clause. When you do so with more and more fields, that means you tend towards making every single row its own group. So yes, this has an influence, and most often not the one you want.

I struggle with understanding what you want from your pure data dump. Can you state this simply in natural language? Especially what is TotalCNoperJob? Is it a count? A sum? A bird, a plane? Superman? What is a CNo? Is it an identifier? It wouldn't make sense to sum customer numbers, for example, as that gives an arbitrary sum value of no meaning, it would make sense to count customers. That's the thing with short column names. They don't make clear, what they mean.

And as unfortunate as a query without data is, as unfortunate is data withut your current query. Only both at hand give a better insight to your problem.

Bye, Olaf.

 

[codrutza]

Thank you both for your patience.

I want to do a report from the data in the output.

JNo JDate Sales TotalCNoperJob
10 21/01/2015 10 5
11 21/01/2015 20 1
12 22/01/2015 30 2
13 23/01/2015 10 1
14 26/02/2015 10 1
15 26/02/2015 40
16 26/02/2015 30 4
17 27/03/2015 20
18 28/03/2015 40
19 29/03/2015 30
20 29/03/2015 60 1
40 22/01/2016 0 1
41 23/01/2015 50 1
42 24/01/2016 50
43 24/01/2016 60
44 23/01/2016 20
45 26/02/2016 10 4
46 26/02/2016 40
47 26/02/2016 50
48 26/02/2016 20

- The report would be much easier to do, because I have to put the totals at the beginning of the report, not at the end. I have to put the totals in the group header year and then the totals by client in group header client. I develop the reports with Crystal Reports, and it lets me to put sum in group header, but putting runningtotals in the group headers of the report is incorrect. That why I wanted a script, a stored procedure that gives me the output above to be able to do sum, instead of running totals. (See Crystal Reports sum aggregate versus running totals if you wish). - I need the detail as well in the report, not only the totals, so practically I need this output. Then I will make the calculations in Crystal Reports. I mean, doesn't really matter what I'll do afterward, from the question point of view, just I tried to explain want I want to do. Don't waist your time with this.-

I'm not familiar with group by clause, I avoided it, but if I understood right, it won't modify the result I want: "means you tend towards making every single row its own group" ???. I'm not sure what info I'd be allowed to give about db structure and data, so I quickly put some imaginary data. Basically I have to have the job no, date, the sales, the margin, the total containers for job - TotalCNoperJob, which is Count containers per job, where CNo is the type char -, the client, ....... on a single line.

If not with group by, I was thinking of something like this, but I'm not sure this is right, I can't try it here, at home.

SELECT JobsE.JNo, JobsE.JDate, JobsE.Sales, JobE………

(SELECT COUNT(CounT.CNo)
FROM Cons AS CountT
WHERE JobsE.JNo=CountT.CJNo) AS CountContJob
FROM Jobs AS JobsE

?

 

[Andrzejek]

Wouldn't it be nice to show/see the data this way?

[pre]
JNo JDate Sales TotalCNoperJob
10 21/01/2015 10 5
11 21/01/2015 20 1
12 22/01/2015 30 2
13 23/01/2015 10 1
14 26/02/2015 10 1
15 26/02/2015 40
16 26/02/2015 30 4
17 27/03/2015 20
18 28/03/2015 40
19 29/03/2015 30
20 29/03/2015 60 1
40 22/01/2016 0 1
41 23/01/2015 50 1
42 24/01/2016 50
43 24/01/2016 60
44 23/01/2016 20
45 26/02/2016 10 4
46 26/02/2016 40
47 26/02/2016 50
48 26/02/2016 20 [/pre]

Please, use [tt][ignore][pre] ... [/pre][/ignore][/tt] tags

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[kjv1611]

I would be tempted to use a CTE or subquery, take your pick. I personally generally prefer CTEs:

;WITH ConsSum AS
(
SELECT CJNo ,COUNT(1) AS Cons
FROM   Cons
GROUP BY CJNo
)

SELECT j.JNo, j.JDate, j.Sales, c.Cons AS TotalCNoperJob
FROM Jobs j
JOIN Cons c
   ON j.JNo = CJNo
ORDER BY j.JNo

Or am I missing something?

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[codrutza]

Thanks Andy.

kjv1611
I read now about CTE, I didn't know about them, thank you.

If I use a CTE:
- it is not clear for me the join: ON j.JNo = CJNo?
- where is the table ConsSum in the second select?
- can you explain me Count(1)?

 

[kjv1611]

Sorry - typo - I meant ConsSum, not Cons.

the join: ON j.JNo = CJNo?

> j = alias for Jobs, c = alias for the CTE, ConsSum​

ConsSum here - the CTE acts somewhat like a temp table in that it acts as its own object. It acts like a Subquery in the same way, jut a different location.

CTE usage can be a really problem solver in several instances that I've used over the years:
[ul]
[li]Make a very large dataset more manageable by first filtering to a smaller number (if possible). For instance, for one mammoth table I'd often have to query against, we had billions of rows in the table, and it was constantly being added to. 99% of the time, when I was looking for something, it'd be at least in the last month, maybe last 1-2 years, tops. I was able to order by the ID column in descending order, and just get the top 10,000 or 10,000,000 - whatever I needed for the particular query - and turn a 5-10 minute (or more) query into 5 seconds or less - sometimes 0 seconds.[/li]
[li]When you need to get multiple different iterations from different pieces of data, a CTE offers great ways to organize, b/c you can use multiple CTEs in the same query.[/li]
[li]If you cannot create local tables for some reason, and need to create steps along the way, a CTE can help bridge that gap.[/li]
[li]Performance: Sometimes using temp tables is faster, and sometimes using CTEs is faster. In my first example, the CTE was faster every time.[/li][/ul]

CTEs are not always the best answer, but I think (from what I understand), it fits here.

COUNT(1):

When you just need a row count, if you simply count the integer, 1, for each row, it can aid in better query engine performance, b/c the engine doesn't have to go and look up each of the values, it just simply adds 1 + 1 + 1 + 1...



"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[codrutza]

Thank you. Can you repeat ;WITH ConsSum AS
(
SELECT CJNo ,COUNT(1) AS Cons
FROM Cons
GROUP BY CJNo
)

SELECT j.JNo, j.JDate, j.Sales, c.Cons AS TotalCNoperJob
FROM Jobs j
JOIN Cons c
ON j.JNo = CJNo
ORDER BY j.JNo

with the correction ConsSum?

I am confused.

 

[kjv1611]

No problem. I admit, when you're first introduced to CTEs it can seem daunting. However, if you can get that under your belt, it's a very powerful tool.

;WITH [b][COLOR=#A40000]ConsSum[/color][/b] AS
(
SELECT [COLOR=#CC0000]CJNo[/color] 
     , COUNT(1) AS [COLOR=#CC0000]ConsCount[/color]
FROM   Cons
GROUP BY CJNo
)

SELECT j.JNo
     , j.JDate
     , j.Sales
     , [b]c[/b].[COLOR=#CC0000]ConsCount AS TotalCNoperJob[/color]
FROM Jobs j
JOIN [b][COLOR=#A40000]ConsSum[/color] c[/b]
   ON j.JNo = [b]c[/b].[COLOR=#CC0000]CJNo[/color]
ORDER BY j.JNo

I did a couple of additional things to hopefully help understand what you're looking at:
[ol 1]
[li]Added dark red color and bold to the CTE name, and bolded the CTE alias ("c") in the final query to hopefully make it easier to follow.[/li]
[li]Added red color to the fields which are carried from CTE to the final query.[/li]
[li]Spaced out the selected fields within the CTE and final query to different lines (that sometimes makes it easier to see and understand what you're looking at with SQL queries).[/li]
[/ol]

Let me know if that gets you there. If you get an error, could be I missed a typo, as I'm just typing here in the text window, not in a SQL window.

By the way, CTE = "Common Table Expression":

https://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[codrutza]

It works, thank you, kjv1611.
Am I right when I say could be more then one CTE? From what I read. I have in the report some other subreports, each with their selection for which I'd like CTEs.

 

[TamarGranor]

Yes, you can have multiple CTEs in a single query. Comma-separate them:

WITH CTE1 AS (...),
     CTE2 AS (...),
     CTE3 AS (...)

SELECT ...

Even better, each CTE can use any of the CTEs before it.

Tamar

 

[kjv1611]

Yes. At times, multiple CTE is a good thing, and at times, a bad thing.

For instance, I had some queries at my last job where I'd have 5+ CTEs in one SQL statement (not just within a script of multiple statements).

You need to think about what you're looking at and how you're using the data. If you use multiple in some situations, you're going to bring your query to a halt. Here's the thing to keep in mind: Every time a CTE references the underlying data, that's increasing the number of reads against the table. So if you have a large table, and build one CTE, and then build another CTE referencing the first CTE, then a third that references the second, then most likely you're going to end up with a mess there. Just not a good idea.

I've had some reports though that given the requirements and what was available to me as an analyst, I'd pull from multiple very large tables, and have different aggregations from the same data.

So, I could have something like:

;WITH BigCarSalesCTE AS (
SELECT TOP 10000 * FROM CarSales.dbo.CarSalesData ORDER BY SalesID DESC
)

, AutoRecalls AS
(
SELECT TOP 10000 RecallID ,RecallDate, VIN, RecallDescription, OrderedBy FROM AutoRecalls.dbo.Recalls ORDER BY RecallID DESC
)

, OverallMonthlySales AS 
(
SELECT CONVERT(nvarchar(6), SalesDate, 112) AS SalesMonth ,COUNT(1) AS Sales ,SUM(SalePrice) AS Revenue 
FROM BigCarSalesCTE
GROUP BY CONVERT(nvarchar(6), SalesDate, 112)
)

, RecallsByMonth AS
(
SELECT CONVERT(nvarchar(6),RecallDate,112) AS RecallMonth ,COUNT(1) AS Recalls
FROM AutoRecalls
GROUP BY CONVERT(nvarchar(6),RecallDate,112)
)

--Then finally bring together whatever details you need...
SELECT -- Fields to suit your final selection
FROM BigCarSalesCTE bc
LEFT JOIN AutoRecalls ar ON bc.VIN = ar.VIN
LEFT JOIN OverallMonthlySales oms ON CONVERT(nvarchar(6), SalesDate, 112) = SalesMonth
LEFT JOIN RecallsByMonth ON CONVERT(nvarchar(6),RecallsByMonth,112) = RecallMonth
ORDER BY SalesDate

That's I'm sure not the best real world example, but I didn't want to take any risk going back and exposing information I might remember from my previous employer.



If it's a query you run often, then it'd pay to first think through it of course, but then test using temp tables vs table variables vs CTE. For some scenarios, creating tables just won't work, of course, and that's where CTEs are the real winners.

I fondly was referred to as "CTE Man" for the last 6 months to a year at my last job. I just LOVE using that tool in SQL, and I'm SO glad Microsoft introduced it.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[codrutza]

;WITH ConsSum AS
(
SELECT CJNo
, COUNT(1) AS ConsCount
FROM Cons
GROUP BY CJNo
)

SELECT j.JNo
, j.JDate
, j.Sales
, c.ConsCount AS TotalCNoperJob
FROM Jobs j
JOIN ConsSum c
ON j.JNo = c.CJNo

ORDER BY j.JNo


I have the select above for j.type=’A’, where I have to have left outer.
More, I have to write a similar select for j.type=’B’, for 3 tables: Jobs, JobsMs, Ms

SELECT j.JN0, M.MNo, M.ContNo, M….
FROM Jobs j
JOIN ConsSumM cm
ON j.JNo = cm.MJNo
JOIN JobsMs
ON j.JNo = JobsMs.MJNo
JOIN JobsMs
ON JobsMs.MNo = Ms.MNo

…..

WHERE j.type=’B’ and M.fel in (‘v’,’s’) …
I join Jobs with JobsMs on the JNo and JobsMs with Ms on the MNo.
For a MNo are one or many JNo. There is one container for one MNo
[/pre]
JNo JDate Sales TotalCN MNo
50 21/01/2015 10 1 m10
51 21/01/2015 20
52 22/01/2015 30
53 23/01/2015 10
54 26/02/2015 10
55 26/02/2015 40 1 m24
56 26/02/2015 30
57 27/03/2015 20
58 28/03/2015 40
59 29/03/2015 30
60 29/03/2015 60 1 m50
70 22/01/2016 0
71 23/01/2015 50
72 24/01/2016 50 m37
48 26/02/2016 20
[/pre]

In the end I have to have data from each select: for type=’A’ type =’B’
For type=’A’ could be jobs without containers but they have to appear having sales
For type=’B’ could be ms without containers but they have to appear having sales on jobs

Or maybe I could group the data on MNo from beginning and then write a CTE only for type='B'? I don't know....
Any help much appreciated.

 

[kjv1611]

Glad you're trying with the [pre] tags, but they work by typing:
[ignore][pre]
SomeContent
[/pre][/ignore]

And it'd be great to use the

 tags around your SQL statements or any other code statements in other forums in the same manner:
[ignore][code]
Your next great code here

[/ignore]



"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

And maybe I'm missing something, but I'm not seeing where your ideas/questions/thoughts line up with the code you've posted. Maybe it'll help to document out in a SQL window just what it is you're trying to accomplish, and then type in the code as SELECT INTO #MyTempTable1 phrases. Test that before trying to do anything else. Make sense?

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[Andrzejek]

Using Preview and checking your post before hitting Submit Post would be beneficial, too.

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[codrutza]

Maybe you help me with something else.
I have to combine in one select 2 CTEs: one which count the containers for a job, and one with sum sales for a job. How do I write this?
1.
;WIH Cont_CTE AS
(
SELECT CJNo
, COUNT(1) AS ContCount
FROM Cons
GROUP BY CJNo
)
2.
;WIH Sales_CTE AS
(
SELECT
J.JNo,
sum(H.Amount*H. Sign) as SumSales

from J
INNER JOIN H
ON substring(H.InvNr,2,7)=J.JNo
and substring(H.InvNr,1,1)='J'
and substring(H.InvNr,9,2)>='/1'
and substring(H.InvNr,9,2)<='/99'
and H. key='ac'
..

GROUP BY J.JNo
)

1 and 2 in:

SELECT
J.JNo ,
Custs.Code CCusCode,
Custs_2_At.Code C2AgCode,
J.Date,
Custs_1_Sen.Name C1SenName,
J.SRef,
..
J.Cer,
J.JT,
J.Via,
Cont_CTE.CountCont,
Sales_CTE.SumSales



FROM
(((Custs INNER JOIN J ON Custs.CustNo=J.CustNo)
LEFT OUTER JOIN Custs Custs_2_At ON J.AtNo=Custs_2_At.CustNo)
INNER JOIN Custs Custs_1_Sen ON J.SenNo=Custs_1_Sen.CustNo)

LEFT OUTER JOIN Cont_CTE
ON Cont_CTE.CJNo=J.JNo)

LEFT OUTER JOIN Sales_CTE
ON Sales_CTE.CJNo=J.JNo

WHERE
Custs.Code='CL’
AND
(
(J.CC='SL' AND J.JT='Imp')
or
J.CC in ('L','K')
)