MS SQL statistical problems-CodePudding

database open full table below

want to statistical output into below such

o: statistics all the consistent of all, the number of duplicate lines (frequency) query command, take time range,

Line title: project code name billing department the implementation date code name of billing department executive department code execution department name price

Anyone predecessors can guide it

CodePudding user response:

 
Select 

 the implementation date, project code 
, the project name 
, billing department code 
, the name of the billing department 
, the implementation of department code 
, the implementation of department name 
, the unit price 
, and count (*) as' frequency '
From table 
Where years between '201901' and '201912', define your own take frequency time 
Group by 

 the implementation date, project code 
, the project name 
, billing department code 
, the name of the billing department 
, the implementation of department code 
, the implementation of department name 
, the unit price

CodePudding user response:

reference 1/f, Yole response:

 
Select 

 the implementation date, project code 
, the project name 
, billing department code 
, the name of the billing department 
, the implementation of department code 
, the implementation of department name 
, the unit price 
, and count (*) as' frequency '
From table 
Where years between '201901' and '201912', define your own take frequency time 
Group by 

 the implementation date, project code 
, the project name 
, billing department code 
, the name of the billing department 
, the implementation of department code 
, the implementation of department name 
, the unit price

Yes there is a problem, the project unit, in table 1 have a project in another table 2 units, how to make project after add units listed in table 1,
There are some projects have negative, how can offset output shows positive and negative value
the result after?Thank you very much!

CodePudding user response:

1, a link can: table 1 left on the join table 2 table 1. The primary key=table 2. The primary key

2, offset due to return, if return need a return tag, on the billing data to corresponding before, and then when the minus the return of the amount, if is 0, be deemed to have been less frequency,

CodePudding user response:

reference Yole reply: 3/f

1, the associated switch: table 1 left on the join table 2 table 1. The primary key=table 2. The primary key

2, offset due to return, if return need a return tag, on the billing data to corresponding before, and then when the minus the return of the amount, if is 0, be deemed to have been less frequency,

Return no tag, just in the unit price for the XX
-This can be offset

CodePudding user response:

No tags, the unit price - XX will be a little trouble to handle: the first thing to filter is not negative frequency, and then choose - XX frequency, finally the relation between two tables do frequency difference,

CodePudding user response:

The

reference 5 floor Yole response:

tag, the unit price - XX will be a little trouble to handle: the first thing to filter is not negative frequency, and then choose - frequency of XX, at last the two tables, frequency difference, do

That to do then, please teach it

CodePudding user response:

 
Select 
A. 
 the implementation date, a. project code 
, a. project name 
, a. billing department code 
, a. billing department name 
, a. executive department code 
, a. executive department name 
, a. unit price 
, a. frequency - isnull (b. frequency, 0) as frequency 
The from 
(select 

 the implementation date, project code 
, the project name 
, billing department code 
, the name of the billing department 
, the implementation of department code 
, the implementation of department name 
, the unit price 
, and count (*) as' frequency '
From table 
Where years between '201901' and '201912', define your own take frequency time 
And the unit price & gt; 0 
Group by 

 the implementation date, project code 
, the project name 
, billing department code 
, the name of the billing department 
, the implementation of department code 
, the implementation of department name 
, the unit price 
) a 
Left the join 
(
Select 

 the implementation date, project code 
, the project name 
, billing department code 
, the name of the billing department 
, the implementation of department code 
, the implementation of department name 
, the unit price 
, and count (*) as' frequency '
From table 
Where years between '201901' and '201912', define your own take frequency time 
And the unit price & lt; 0 
Group by 

 the implementation date, project code 
, the project name 
, billing department code 
, the name of the billing department 
, the implementation of department code 
, the implementation of department name 
, the unit price 
B) 
Where a. frequency - isnull (b. frequency, 0) & gt; 0

CodePudding user response:

refer to 7th floor Yole response:

 
Select 
A. 
 the implementation date, a. project code 
, a. project name 
, a. billing department code 
, a. billing department name 
, a. executive department code 
, a. executive department name 
, a. unit price 
, a. frequency - isnull (b. frequency, 0) as frequency 
The from 
(select 

 the implementation date, project code 
, the project name 
, billing department code 
, the name of the billing department 
, the implementation of department code 
, the implementation of department name 
, the unit price 
, and count (*) as' frequency '
From table 
Where years between '201901' and '201912', define your own take frequency time 
And the unit price & gt; 0 
Group by 

 the implementation date, project code 
, the project name 
, billing department code 
, the name of the billing department 
, the implementation of department code 
, the implementation of department name 
, the unit price 
) a 
Left the join 
(
Select 

 the implementation date, project code 
, the project name 
, billing department code 
, the name of the billing department 
, the implementation of department code 
, the implementation of department name 
, the unit price 
, and count (*) as' frequency '
From table 
Where years between '201901' and '201912', define your own take frequency time 
And the unit price & lt; 0 
Group by 

 the implementation date, project code 
, the project name 
, billing department code 
, the name of the billing department 
, the implementation of department code 
, the implementation of department name 
, the unit price 
B) 
Where a. frequency - isnull (b. frequency, 0) & gt; 0

There's no way.

refer to 7th floor Yole response:

Select
A.
the implementation date, a. project code
, a. project name
, a. billing department code
, a. billing department name
, a. executive department code
, a. executive department name
, a. unit price
, a. frequency - isnull (b. frequency, 0) as frequency
The from
(select

the implementation date, project code
, the project name
, billing department code
, the name of the billing department
, the implementation of department code
, the implementation of department name
, the unit price
, and count (*) as' frequency '
From table
Where years between '201901' and '201912', define your own take frequency time
And the unit price & gt; 0
Group by

the implementation datenullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull