Ask for a SQL statement that counts the total sales per day, per month, and per year

This is definitely not going to work!

You should understand the SQL execution order.

SQL is executed first to find select * from *

Then there's the filter where

Again, the grouping function group by

Finally, there is order by

The condition of the group by function is that the column used for grouping must appear in the query, and your to char(,'yyyy-mm-dd') is not considered a column.

The simplest solution is to alias them separately and treat them as tables and then query them in groups.

i.e. select column aliases from (your statement) alias group by column aliases.

That way there will be no problems.

But if you check it like this, you might as well check it directly on the table.

select , from table1 t1,table t2

where t1 = t2 and = 1 and to_char(,'yyyy-mm-dd') = '2010-10-01'

Your usage of in is also not right, what it needs is a result set.

If you have to write in as follows: select count(*)from table2 where callid in (select callid from table1 where type = 1 and to char(createdate,'yyyy-mm-dd') = '2010-10-01'

To continue adding: if you use the upstairs at least need to be modified to look like this.

select , select count(*)from table2 a2 where in (select callid from table1 a3 where to_char(,'yyyy-mm-dd')=to_char(,'yyyy-mm-dd') and

from (

select to_char(,'yyyy-mm-dd') as createdate,from table1 a1 ) a1

group by ,

That's too cumbersome.

As long as there is a problem in your in statement.

select to_char(,'yyyy-mm-dd'), count(*)

select count(*)from table2 a2 where in (select callid from table1 a3 where to_char(,'yyyy-mm-dd')=to_char(,'yyyy-mm-dd') and

from table1 a1 group by to_char(,'yyyy-mm-dd'),

select year(ordertime) year, sum(total) total sales.

from the order form.

group by year(ordertime)

2. SQL statements count the total sales per month.

select year(ordertime), month(ordertime), sum(total) total sales.

from the order form.

group by year(ordertime)，month(ordertime

3. SQL statements count the total sales volume per day.

select year(ordertime), month(ordertime), day(ordertime), sum(total) total sales.

from the order form.

group by year(ordertime)，month(ordertime),day(ordertime)

1. Create a test table, 62616964757a686964616fe78988e69d8331333431373863

create table test_stu(id number, u_name varchar2(20), subject varchar2(20));

create table test_subj(id number, subject varchar2(20));

2. Insert test data.

insert into test_stu values(1,'Zhang San','English');

insert into test_stu values(2,'Li Si','German');

insert into test_stu values(3,'Wang V','Japanese');

insert into test_stu values(4,'Xiao Ming','English');

insert into test_stu values(5,'Puppies','French');

insert into test_subj values(1,'English');

insert into test_subj values(2,'German');

insert into test_subj values(3,'Japanese');

insert into test_subj values(4,'French');

3. Query the number of all records in the table, select t*,rowid from test subj t,4、Write SQL,count the total number of test subj records, and the number of elective students in each subject;

select count(distinct as "Subtotal",count(case when subject='English' then 1 end) as "English",count(case when subject='German' then 1 end) as "German",count(case when subject='Japanese' then 1 end) as "Japanese"

from (select t.*

from test_subj t, test_stu b

where = t

sqlserver as an example.

Create tables and insert data.

create table name list.

id int,u_name varchar(10),subject varchar(10))

Create Table chart of accounts.

id int,s_name varchar(10))

insert into namesheet values (1,'Zhang San','English

62616964757a686964616fe59b9ee7ad9431333337373562')

insert into namesheet values (2,.'Li Si','German')

insert into the name table values (3,.'Wang V','Japanese')

insert into namesheet values (4,.'Xiao Ming','English')

insert into namesheet values (5,.'Puppies','French')

insert into Chart of Accounts values (1,'English')

insert into Chart of Accounts values(2,'German')

insert into Chart of Accounts values(3,'Japanese')

insert into Chart of Accounts values(4,'French')

Then you need to create a view.

create view v_subject

asselect ,sum(case when then 1 else 0 end) counts

from Chart of Accounts A Left Join Name Table B on

group by

Execute the statement. declare @sql varchar(4000)

set @sql = 'select sum(counts) as total'

select @sql = @sql + ',sum(isnull(case [s_name] when '''+[s_name]+''' then [counts] end,0)) as

+[s_name]+']'

from (select distinct [s_name] from v_subject) as a

select @sql = @sql+' from [v_subject]'

exec (@sql)

Screenshot of the results. Why do you have less results?

This is mainly a dynamic display that is so complicated, for example, if you add an Arabic word to the chart of accounts, it is no problem to use this, otherwise it will be more limited to use case when.

The method and detailed operation steps are as follows:

1. The first step is to create a test table, see the figure below for details, and go to the steps below.

2. In the second step, after executing the above DU operation, insert the test data, see the following DAO diagram for details, and go to the following steps. Genus.

3. In the third step, after performing the above operations, record in the query table, see the figure below and go to the steps below.

4. In the fourth step, after performing the above operations, write SQL, group the records for statistics, record the number of groups, and the result is 4 groups, see the figure below. In this way, the problem is solved.

Ask the subject to complete the question and explain the needs in detail.