Let me know some example of advance sql query

Hello everyone

I am not  sql expert so let me ask some questions.

I have 3 tables.

Table_A , Table_B and Table_C.(I want to create table)

How can I create Table_C.

Please check my picture below

How can I achieve this .Please help me.

Hi May,

If Table_A and Table_B are not related, then how do you aim to join it to created Table_C? There must be some unique and common column in both the entities.

Or, do you simply want the count of both the tables, which means Table_C will have only one (active) record all the time?

Swatantra Kumar wrote:

Thanks you for your reply.

Actually there are alot of data in table_A and table_B (at least 360 records for one year 2019/01/01 ~ 2020/01/01)

I want to count data by month and calculate  and then insert result in table_C like my picture.

Or, do you simply want the count of both the tables, which means Table_C will have only one (active) record all the time?



Hello May.

SQL is the hardest part of programming. You should really focus on that before trying to be creative. There is probably an easier way of doing the same and it will depend on the problem you have.


The image you are showing can be

Select Count(TableA.A1), Count(TableA.A2), Count(TableA.A3),
Count(TableA.A1)+Count(TableA.A2)+Count(TableA.A3),
Select Count(TableB.B1), Count(TableB.B2),
Count(TableB.B1) * Count(TableB.B2)
From TableA, TableB

But this is pointless because all the counts of A will have the same number. The same goes for B.

If A has 100 rows and B 20, the columns will be 100,100,100,300,20,20,400



If you want to group by month, you must have a column with the month.

Select TableA.month, Count(TableA.A1)

From TableA

Group by TableA.month


Then a whole batch of questions shows up. Both tables have all months or only one of them? Or they may have different months between them?

Assuming TableA as all the months you can do:

Select TableA.month, Count(TableA.A1), Count(TableB.B1)

From TableA

Left join TableB on TableA.month=TableB.month

Group by TableA.month


Now, do you need to save it into TableC or just want to use the data here?

Because you can return as a structure and be done with it.


Otherwise, you need to add on top of the query (assuming the id is autonumber)

Insert into TableC (C1,C2,C3,C4,C5,C6,C7)




May Zin Linn wrote:

Hello everyone

I am not  sql expert so let me ask some questions.

I have 3 tables.

Table_A , Table_B and Table_C.(I want to create table)

How can I create Table_C.

Please check my picture below

How can I achieve this .Please help me.

Hi,

Please follow the below steps,


1. Create Output structure like Table C

2. Use Advanced SQL widget and refer below query,

insert into Table_C
select sum(T.A1),sum(T.A2),sum(T.A3),sum(T.Count_A),sum(T.B1),sum(T.B2),sum(T.B3),sum(T.Count_B)
from
(
select COUNT(A1) as A1,COUNT(A2) as A2,COUNT(A3) as A3,COUNT(A1)+COUNT(A2)+COUNT(A3) as Count_A, 0 as B1,0 as B2,0 as B3,0 as Count_B from Table_A
union all
select 0 as A1,0 as A2,0 as A3,0 as Count_A,COUNT(B1) as B1,COUNT(B2) as B2,COUNT(B3) as B3,COUNT(B1)*COUNT(B2)*COUNT(B3) as Count_B from Table_B
) as T


Table Structure:


CREATE TABLE [dbo].[Table_A](
[A1] [nvarchar](50) NULL,
[A2] [nvarchar](50) NULL,
[A3] [nvarchar](50) NULL
)


CREATE TABLE [dbo].[Table_B](
[B1] [nvarchar](50) NULL,
[B2] [nvarchar](50) NULL,
[B3] [nvarchar](50) NULL
)


CREATE TABLE [dbo].[Table_C](
[A1] [nvarchar](50) NULL,
[A2] [nvarchar](50) NULL,
[A3] [nvarchar](50) NULL,
[B1] [nvarchar](50) NULL,
[B2] [nvarchar](50) NULL,
[B3] [nvarchar](50) NULL,
[Count_A] [nvarchar](50) NULL,
[Count_B] [nvarchar](50) NULL
)


Please let me know.