New to SQL - How to group by Customer wise, item wise, city wise and country wis

Question

Post reply

New to SQL - How to group by Customer wise, item wise, city wise and country wis

Zafar007

SSC Enthusiast

Points: 113

June 21, 2020 at 6:48 pm

#3764600

Dear Developers - I am new to coding, how can I group the below tables by:

1. Customer wise

2. Item wise

3. City wise

4. Country-wise


CREATE TABLE customer (
    id      INTEGER,
    name    TEXT,
    mobile  INTEGER,
    city_id INTEGER
);

CREATE TABLE items (
    id         INTEGER,
    name       TEXT,
    unit_price INTEGER
);

CREATE TABLE city (
    id      INTEGER,
    name    TEXT,
    city_id INTEGER
);

CREATE TABLE country (
    id         INTEGER,
    name       TEXT
);

CREATE TABLE sale (
    transaction_id       [ID INTEGER],
    date                 INTEGER,
    customer_id          INTEGER,
    item_id              INTEGER,
    quantity             INTEGER
);

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63599 More actions · Answer 1

pietlinden

SSC Guru

Points: 63599

June 22, 2020 at 12:32 am

#3764609

have you looked up GROUP BY yet?

Zafar007 SSC Enthusiast Points: 113 More actions · Answer 2

Zafar007

SSC Enthusiast

Points: 113

June 22, 2020 at 6:08 am

#3764624

Yes

pietlinden SSC Guru Points: 63599 More actions · Answer 3

So where are you stuck? Post the SQL statement(s), so we can figure out how to help you.

Mr. Brian Gale SSC-Insane Points: 24968 More actions · Answer 4

Looking at your tables, do you have any keys on them? If so, then it is pretty easy to do. If you have no keys on it, then it is entirely guesswork as to how the data is related.

For example, CITY_ID in the table CITY I am not certain what it should point to. My guess is it is supposed to be COUNTRY_ID, but it is currently just guessing.

The next step is what data are you trying to get from that? You say you want to group the data, but are you wanting to select all of the data and then group it by a specific thing? If that is the case, you should look at ORDER BY rather than GROUP BY as you are just ordering it by a specific column such as CITY. NOTE - this assumes you are not doing any aggregates on the data.

If you are doing aggregates on the data, then you are going to either need a GROUP BY or a windowing function.

Knowing what data you are trying to select will GREATLY help us help you.

Also, with SQL you are not grouping the tables, but grouping the rows.

What I mean by using "ORDER BY" is something like this:

SELECT 
    transaction_id       ,
    date                 ,
    item_id              ,
    quantity             ,
    name                 ,
    mobile             

FROM [sale]
JOIN [customer] on [sale].[customer_id]=[customer].[id]
ORDER BY [sale].[customer_id] ASC;

Using the above query, the data from SALES will be ordered by customer_id. We also join that over to the Customer table and you'd have a list of all your customers with the customers "grouped" together because they are ordered by the customer_id.

The query you posted though will fail in SQL Server because:

    transaction_id       [ID INTEGER],

is not valid TSQL. [ID Integer] is not a valid datatype. What you probably want is:

    transaction_id     INTEGER  IDENTITY(0,1) NOT NULL PRIMARY KEY,

which will give you an ever increasing key. Not sure if that is the best primary key, but it depends on how the transaction_id is used. It may make sense for it to be the PK.

Also, depending on the number of sales you expect, you may want to change the transaction_id to a BIGINT or possibly even a GUID.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

jcelko212 32090 SSCrazy Eights Points: 9304 More actions · Answer 5

Your datatypes are completely wrong and your tables are simply disconnected badly done files with no relation to an RDBMS at all. You have no keys! Have you ever had a single course in databases and learned about normalization? Why do you believe in a magic "id" that identifies anything in the universe? TEXT is not part of SQL. Identifiers are never numerics. Etc.

I'm going to make some guesses and try and get the schema design down correct first. I'm sorry you have only one customer as shown by your singular name. I'm sorry you don't know any of the standard encoding schemes.

CREATE TABLE Customers

(customer_id CHAR(16) NOT NULL PRIMARY KEY, – not an option

cust_name VARCHAR(35) NOT NULL,

mobile_nbr CHAR(10) NOT NULL,

city_id CHAR(5) NOT NULL

REFERENCES Cities(city_id );

CREATE TABLE Items

(gtin CHAR(15) NOT NULL PRIMARY,

item_name VARCHAR(35) NOT NULL,

unit_price DECIMAL (8,2) NOT NULL

CHECK (unit_price > 0.00));

CREATE TABLE Cities

(city_id CHAR(5) NOT NULL PRIMARY KEY,

city_name VARCHAR(35) NOT NULL,

country_code CHAR(3) NOT NULL, ---iso standard

);

CREATE TABLE Sales

(transaction_id CHAR(10) NOT NULL PRIMARY KEY,

trans_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,

customer_id CHAR(16) NOT NULL

REFERENCES Customers(customer_id)

gtin CHAR(15) NOT NULL

REFERENCES Items(gtin),

trans_qty INTEGER NOT NULL

CHECK(trans_qty > 0));

See how the tables relate to each other? There are REFERENCES. Look at the data types. Learn what a GTIN (Global Trade Item Number) is. Look at CHECK() constraints. Now, I have no idea what you want, so I will guess you meant this:

SELECT COUNT(DISTINCT customer_id) AS distinct_customer_cnt

FROM Sales;

GROUP BY customer_id;

Please post DDL and follow ANSI/ISO standards when asking for help.