Query Count/SUM on multiple tables

Question

I have a database with the following tables:

CREATE TABLE PAR (
    ID          varchar(10) not null,
    Name                varchar(20) not null,
    State               varchar(20) not null,
    primary key (ID)
);




INSERT INTO PAR (ID, Name, State)
VALUES
('A000','Susan','New York'),
('B123','Bob','Texas'),
('C456','Tony','Washington'),
('D789','Adam','California');


CREATE TABLE GROUP (
    GroupID         varchar(25) not null,
    Person1         varchar(15) not null,
    Person2         varchar(15) not null,
    Person3         varchar(15),
    Person4         varchar(15),
    primary key (GroupID)
    );


INSERT INTO GROUP (GroupID, Person1, Person2, Person3, Person4)
VALUES
('G234','A000','B666','C777'),
('G456','B123','D898','E632'),
('G789','C456', null, null),
('G444','D789','O123', null);


CREATE TABLE SOLO (
    Classification          varchar(15) not null,
    ID      varchar(10) not null,
    Fruits          varchar(10) not null,
    primary key (Classification, ID)
    foreign key (ID) references PAR(ID)
);

INSERT INTO SOLO(Classification, ID, Fruits)
VALUES
('A Group','A000','Apple'),
('A Group','B123','Banana'),
('B Group','C456','Orange');


CREATE TABLE GROUPINGS (
    Classification      varchar(20) not null,
    Group           varchar(25) not null,
    Fruits          varchar(15) not null,
    primary key (Classification, Group)

    foreign key (Group) references GROUP(GroupID)
);

INSERT INTO GROUPINGS(Classification, Group, Fruits)
VALUES
('A Group','G234','Apple'),
('B Group','G456','Grape'),
('A Group','G789','Banana'),
('D Group','G444','Orange');

I am trying to get the following result from my query:

State|  Fruits
------+-------
New York| 2
Texas   | 2
Washington| 2
California| 1

I created a query that looks like this but it does not work: I know that I need to sum up all the different fruits from the tables and I have use common keys in order to this but I am not sure how as I am quite new to SQL queries

SELECT P.State, COUNT(S.ID) + COUNT(z.Group) as gSum
FROM PARTICIPANT AS P, SOLO AS S,
GROUPINGS AS G, GROUP as Z
WHERE z.GroupID = G.Group
GROUP BY P.STATE

I need to figure out how I can link the ID key in PAR table with each person column in GROUP table

user_17296795 · Answer

First, are you sure that all statements executed successfully!? In your code:

CREATE TABLE GROUP ( 
GroupID varchar(25) not null,
Person1 varchar(15) not null, 
Person2 varchar(15) not null,
Person3 varchar(15) not null, 
Person4 varchar(15) not null, 
primary key (GroupID) );


INSERT INTO GROUP 
(GroupID, Person1, Person2, Person3, Person4) 
VALUES
('G234','A000','B666','C777'), 
('G456','B123','D898','E632'), 
('G789','C456', null, null), 
('G444','D789','O123','D225',null);

All columns specified with not null constraint. But in insert code,first error is that you send null values, second is that you have 5 columns but sent only 4 values.

You can edit the insert statements and send correct and complete values. Remove any null value or change the table constraints to accept nulls.

Second, in the select statement, you don't need to use sum function that way, the math add operation will get the sum of the count. Your code maybe like:

SELECT P.State, COUNT(S.ID) + COUNT(z.Group) as gSum
FROM PARTICIPANT AS P, SOLO AS S,
GROUPINGS AS G, GROUP as Z
WHERE z.GroupID = G.Group
GROUP BY P.STATE

The above code is with independent of if there are others errors in the conditions or in the data inserted.

Last, you didn't tell us what the error you faced, and what messages error showed!.

Tell us more information to help us help you.

Query Count/SUM on multiple tables

1 Answers