CODEWITHSUNDEEP
sqloracle-database
Tim
Tim

Reputation: 11

Select count inner join two database

I want to show all supplier information sorted by country, including number of names supplied in current month.

Here are the two database and their variables:

1.Supplier: SupplierID, Suppliedname, Country, ReliabilityScore, ContactInfo

2.SuppliedName: SnID, Suppliedname1, Language, Status, Standard, PlaceID,SupplierID, Supplieddate

Here's what I came up with which didn't really work: 

SELECT COUNT(DISTINCT SuppliedName.suppliedname1), Supplier.(*) FROM SuppliedName 
INNER JOIN Supplier ON Supplier.SupplierID = SuppliedName.SupplierID
WHERE EXTRACT(MONTH FROM SuppliedName.Supplieddate)=EXTRACT(MONTH FROM sysdate) AND EXTRACT(YEAR FROM SuppliedName.Supplieddate)=EXTRACT(YEAR FROM sysdate)
ORDER BY Supplier.Country;

Please help!!! Thanks!

Upvotes: 1

Views: 49

Answers (1)

sticky bit
sticky bit

Reputation: 37487

I guess you just missed to GROUP BY all columns from SUPPLIER.

SELECT COUNT(DISTINCT SUPPLIEDNAME.SUPPLIEDNAME1),
       SUPPLIER.SUPPLIERID,
       SUPPLIER.SUPPLIEDNAME,
       SUPPLIER.COUNTRY,
       SUPPLIER.RELIABILITYSCORE,
       SUPPLIER.CONTACTINFO
       FROM SUPPLIEDNAME
            INNER JOIN SUPPLIER
                       ON SUPPLIER.SUPPLIERID = SUPPLIEDNAME.SUPPLIERID
       WHERE EXTRACT(MONTH FROM SUPPLIEDNAME.SUPPLIEDDATE) = EXTRACT(MONTH FROM SYSDATE)
             AND EXTRACT(YEAR FROM SUPPLIEDNAME.SUPPLIEDDATE) = EXTRACT(YEAR FROM SYSDATE)
       GROUP BY SUPPLIER.SUPPLIERID,
                SUPPLIER.SUPPLIEDNAME,
                SUPPLIER.COUNTRY,
                SUPPLIER.RELIABILITYSCORE,
                SUPPLIER.CONTACTINFO
       ORDER BY SUPPLIER.COUNTRY;

Upvotes: 1

Related Questions