Search the Community
Showing results for tags 'Group By'.
-
hi, sorry, i have a question to do...but i know how to write in sql query ....however the answer need to write on relational algebra in query is like : SELECT * FROM operation where r='broken' group by pc having count(pc) >1 ; π id,pc,result ( σ r = 'broken' (operation)) like group by and having clasue...i no idea ... i searched on internet still @@ .... isnt the answer like that, i using split/step by step method to do rel 1 = σ r = 'broken' (operation) rel 2 = σ count(pc)>1 γ pc,count(pc) (rel 1)) rel 3 = π id,pc,result (rel 2 ) thanks
- 2 replies
-
- sql
- relational algebra
-
(and 8 more)
Tagged with:
-
hi there, the following query was developed and works as it should. SELECT ( SELECT COUNT(DISTINCT SP1.bigint_ProspectID, PLS1.smallint_ProspectOrdinal) FROM 10_serviceprospects SP1 LEFT JOIN 13_prospectleadsent PLS1 ON SP1.bigint_ProspectID = PLS1.bigint_ProspectID WHERE PLS1.timestamp_Sent BETWEEN "2013-08-01 00:00:00" AND "2013-08-30 23:59:59" AND SP1.bigint_ServiceID = SS.bigint_ServiceID AND PLS1.bigint_SupplierID = S.bigint_SupplierID ) AS `Previous Month`, ( SELECT COUNT(DISTINCTROW SP2.bigint_ProspectID, PLS2.smallint_ProspectOrdinal) FROM 10_serviceprospects SP2 LEFT JOIN 13_prospectleadsent PLS2 ON SP2.bigint_ProspectID = PLS2.bigint_ProspectID WHERE PLS2.timestamp_Sent BETWEEN "2013-09-01 00:00:00" AND "2013-09-31 23:59:59" AND SP2.bigint_ServiceID = SS.bigint_ServiceID AND PLS2.bigint_SupplierID = S.bigint_SupplierID ) AS `Last Month`, ( SELECT COUNT(DISTINCT SP3.bigint_ProspectID, PLS3.smallint_ProspectOrdinal, PLS3.bigint_SupplierID) FROM 10_serviceprospects SP3 LEFT JOIN 13_prospectleadsent PLS3 ON SP3.bigint_ProspectID = PLS3.bigint_ProspectID WHERE PLS3.timestamp_Sent BETWEEN "2013-10-01 00:00:00" AND "2013-10-30 23:59:59" AND SP3.bigint_ServiceID = SS.bigint_ServiceID AND PLS3.bigint_SupplierID = S.bigint_SupplierID ) AS `Current Month`, ( SELECT COUNT(DISTINCT SP0.bigint_ProspectID, PLS0.smallint_ProspectOrdinal) FROM 10_serviceprospects SP0 LEFT JOIN 13_prospectleadsent PLS0 ON SP0.bigint_ProspectID = PLS0.bigint_ProspectID WHERE PLS0.timestamp_Sent BETWEEN "2013-08-01 00:00:00" AND "2013-10-30 23:59:59" AND SP0.bigint_ServiceID = SS.bigint_ServiceID AND PLS0.bigint_SupplierID = S.bigint_SupplierID ) AS `Total`, S.text_SupplierName AS `Supplier Name` FROM 5_suppliers S JOIN 4_servicesuppliers SS ON (S.bigint_SupplierID = SS.bigint_SupplierID) WHERE SS.bigint_ServiceID = 1 # Bakkie Canopies OnlyAND S.smallint_SupplierStatus = 4 # Freemium Suppliers OnlyGROUP BY S.bigint_SupplierID ORDER BY `Total` DESC, `Supplier Name` ASC; # Order by Total, then Supplier Name took about a day to develop this inbetween other work. it returns the correct data. the output is available at https://performatix.co/csv/Bakkie_Canopies_Freemium_Supplier_Opportunities_2013-10-09.csv i was then requested to change the ordering by `Total` and primarily by `Region Description`; as well as grouping by Region ID and primarily by Supplier ID, creating a secondary output. SELECT R.text_RegionDescription AS `Region Description`, ( SELECT COUNT(DISTINCT SP1.bigint_ProspectID, PLS1.smallint_ProspectOrdinal) FROM 10_serviceprospects SP1 LEFT JOIN 13_prospectleadsent PLS1 ON SP1.bigint_ProspectID = PLS1.bigint_ProspectID WHERE PLS1.timestamp_Sent BETWEEN "2013-08-01 00:00:00" AND "2013-08-30 23:59:59" AND SP1.bigint_ServiceID = SS.bigint_ServiceID AND PLS1.bigint_SupplierID = S.bigint_SupplierID AND SP1.bigint_RegionID = R.bigint_RegionID ) AS `Previous Month`, ( SELECT COUNT(DISTINCTROW SP2.bigint_ProspectID, PLS2.smallint_ProspectOrdinal) FROM 10_serviceprospects SP2 LEFT JOIN 13_prospectleadsent PLS2 ON SP2.bigint_ProspectID = PLS2.bigint_ProspectID WHERE PLS2.timestamp_Sent BETWEEN "2013-09-01 00:00:00" AND "2013-09-31 23:59:59" AND SP2.bigint_ServiceID = SS.bigint_ServiceID AND PLS2.bigint_SupplierID = S.bigint_SupplierID AND SP2.bigint_RegionID = R.bigint_RegionID ) AS `Last Month`, ( SELECT COUNT(DISTINCT SP3.bigint_ProspectID, PLS3.smallint_ProspectOrdinal, PLS3.bigint_SupplierID) FROM 10_serviceprospects SP3 LEFT JOIN 13_prospectleadsent PLS3 ON SP3.bigint_ProspectID = PLS3.bigint_ProspectID WHERE PLS3.timestamp_Sent BETWEEN "2013-10-01 00:00:00" AND "2013-10-30 23:59:59" AND SP3.bigint_ServiceID = SS.bigint_ServiceID AND PLS3.bigint_SupplierID = S.bigint_SupplierID AND SP3.bigint_RegionID = R.bigint_RegionID ) AS `Current Month`, ( SELECT COUNT(DISTINCT SP0.bigint_ProspectID, PLS0.smallint_ProspectOrdinal) FROM 10_serviceprospects SP0 LEFT JOIN 13_prospectleadsent PLS0 ON SP0.bigint_ProspectID = PLS0.bigint_ProspectID WHERE PLS0.timestamp_Sent BETWEEN "2013-08-01 00:00:00" AND "2013-10-30 23:59:59" AND SP0.bigint_ServiceID = SS.bigint_ServiceID AND PLS0.bigint_SupplierID = S.bigint_SupplierID AND SP0.bigint_RegionID = R.bigint_RegionID ) AS `Total`, S.text_SupplierName AS `Supplier Name` FROM 5_suppliers S JOIN 4_servicesuppliers SS ON (S.bigint_SupplierID = SS.bigint_SupplierID) JOIN 1_regions R ON (SS.bigint_RegionID = R.bigint_RegionID)WHERE SS.bigint_ServiceID = 1 # Bakkie Canopies OnlyAND S.smallint_SupplierStatus = 4 # Freemium Suppliers OnlyGROUP BY SS.bigint_RegionID, SS.bigint_SupplierID ORDER BY `Total` DESC, `Region Description` ASC; # Order by Total, then Region Description this took about an hour inbetween other work. the results are in https://performatix.co/csv/Bakkie_Canopies_Regional_Freemium_Supplier_Opportunities_2013-10-09.csv however you will notice that the values do not corroborate with the first (this was discovered recently after reviewing the csv's! what is wrong with this query to get it to corroborate with the first?). i was then asked to order by `Total` but primarily by `Region Description`, and group by Region ID - creating a third output. SELECT R.text_RegionDescription AS `Region Description`, ( SELECT COUNT(DISTINCT SP1.bigint_ProspectID, PLS1.smallint_ProspectOrdinal) FROM 10_serviceprospects SP1 LEFT JOIN 13_prospectleadsent PLS1 ON SP1.bigint_ProspectID = PLS1.bigint_ProspectID WHERE PLS1.timestamp_Sent BETWEEN "2013-08-01 00:00:00" AND "2013-08-30 23:59:59" AND SP1.bigint_ServiceID = SS.bigint_ServiceID AND PLS1.bigint_SupplierID = S.bigint_SupplierID AND SP1.bigint_RegionID = R.bigint_RegionID ) AS `Previous Month`, ( SELECT COUNT(DISTINCTROW SP2.bigint_ProspectID, PLS2.smallint_ProspectOrdinal) FROM 10_serviceprospects SP2 LEFT JOIN 13_prospectleadsent PLS2 ON SP2.bigint_ProspectID = PLS2.bigint_ProspectID WHERE PLS2.timestamp_Sent BETWEEN "2013-09-01 00:00:00" AND "2013-09-31 23:59:59" AND SP2.bigint_ServiceID = SS.bigint_ServiceID AND PLS2.bigint_SupplierID = S.bigint_SupplierID AND SP2.bigint_RegionID = R.bigint_RegionID ) AS `Last Month`, ( SELECT COUNT(DISTINCT SP3.bigint_ProspectID, PLS3.smallint_ProspectOrdinal, PLS3.bigint_SupplierID) FROM 10_serviceprospects SP3 LEFT JOIN 13_prospectleadsent PLS3 ON SP3.bigint_ProspectID = PLS3.bigint_ProspectID WHERE PLS3.timestamp_Sent BETWEEN "2013-10-01 00:00:00" AND "2013-10-30 23:59:59" AND SP3.bigint_ServiceID = SS.bigint_ServiceID AND PLS3.bigint_SupplierID = S.bigint_SupplierID AND SP3.bigint_RegionID = R.bigint_RegionID ) AS `Current Month`, ( SELECT COUNT(DISTINCT SP0.bigint_ProspectID, PLS0.smallint_ProspectOrdinal) FROM 10_serviceprospects SP0 LEFT JOIN 13_prospectleadsent PLS0 ON SP0.bigint_ProspectID = PLS0.bigint_ProspectID WHERE PLS0.timestamp_Sent BETWEEN "2013-08-01 00:00:00" AND "2013-10-30 23:59:59" AND SP0.bigint_ServiceID = SS.bigint_ServiceID AND PLS0.bigint_SupplierID = S.bigint_SupplierID AND SP0.bigint_RegionID = R.bigint_RegionID ) AS `Total`FROM 5_suppliers S JOIN 4_servicesuppliers SS ON (S.bigint_SupplierID = SS.bigint_SupplierID) JOIN 1_regions R ON (SS.bigint_RegionID = R.bigint_RegionID)WHERE SS.bigint_ServiceID = 1 # Bakkie Canopies OnlyAND S.smallint_SupplierStatus = 4 # Freemium Suppliers OnlyGROUP BY SS.bigint_RegionID ORDER BY `Total` DESC, `Region Description` ASC; # Order by Total, then Region Description this took approximately another hour between other work. the results are in https://performatix.co/csv/Bakkie_Canopies_Regional_Freemium_Supplier_Opportunities_2013-10-09.csv once again - the data does not corroborate with the first csv, the issue i am facing is that i do not understand why the queries are not synchronous to the data... the first query works just as well for a different service, however the second and third queries do not return anything at all. i am assuming that this is because they do not have varying regions (all the same). further than this i just keep hitting my head... anyone comfortable enough to have a look at the queries and suggest changes other than the ones i applied? the data from the tables are available at https://performatix.co/csv/1_regions, 2_servicescatalogue, 4_servicesuppliers, 5_suppliers, 10_serviceprospects & 13_prospectleadsent.csv.