Total expenses by department

SQL query for Power Office

Calculate the total expenses incurred by each department.

SELECT d.department_name, SUM(oi.amount) AS total_expenses FROM department d INNER JOIN outgoinginvoice oi ON d.department_id = oi.department_id GROUP BY d.department_name

Power Office data model

Table Product

Id: Integer
Code: String
Name: String
Type: Integer
Unit: String
VatCode: String
IsActive: Boolean
CostPrice: Number
SalesPrice: Number
LastChanged: Date-time
SalesAccount: Integer
ProductGroupId: Integer
UnitOfMeasureCode: Integer
VatExemptSalesAccount: Integer

Table Productgroup

Id: Integer
Code: String
Name: String
Type: Integer
Unit: String
CostPrice: Number
SalesPrice: Number
LastChanged: Date-time
SalesAccount: Integer
VatExemptSalesAccount: Integer

Table Customer

Id: Integer
Code: Integer
Name: String
Since: Date-time
IsActive: Boolean
IsPerson: Boolean
IsVatFree: Boolean
LegalName: String
VatNumber: String
IsArchived: Boolean
WebsiteUrl: String
CreatedDate: Date-time
LastChanged: Date-time
PhoneNumber: String
EmailAddress: String
ExternalCode: Integer
UseFactoring: Boolean
SendReminders: Boolean
UseInvoiceFee: Boolean
MailAddress_id: Integer
ContactPersonId: Integer
MailAddress_city: String
StreetAddress_city: String
CustomerCreatedDate: Date-time
InvoiceDeliveryType: Integer
InvoiceEmailAddress: String
MailAddress_zipCode: String
MailAddress_address1: String
ReminderEmailAddress: String
InvoiceEmailAddressCC: String
MailAddress_isPrimary: Boolean
StreetAddress_zipCode: String
StreetAddress_address1: String
DoNotAddLatePaymentFees: Boolean
MailAddress_countryCode: String
MailAddress_lastChanged: Date-time
StreetAddress_isPrimary: Boolean
StreetAddress_countryCode: String
StreetAddress_lastChanged: Date-time
DoNotAddLatePaymentInterest: Boolean
TransferToDebtCollectionAgency: Boolean

Table Department

Id: Integer
Code: String
Name: String
IsActive: Boolean
LastChanged: Date-time

Table Employee

Id: Integer
Code: Integer
Gender: Integer
LastName: String
FirstName: String
IsArchived: Boolean
CreatedDate: Date-time
DateOfBirth: Date-time
LastChanged: Date-time
PhoneNumber: String
EmailAddress: String
MailAddress_id: Integer
MailAddress_city: String
StreetAddress_city: String
EmployeeCreatedDate: Date-time
InternationalIdType: Integer
MailAddress_zipCode: String
PayrollEmailAddress: String
MailAddress_address1: String
SocialSecurityNumber: String
MailAddress_isPrimary: Boolean
ReportInternationalId: Boolean
StreetAddress_zipCode: String
StreetAddress_address1: String
MailAddress_countryCode: String
MailAddress_lastChanged: Date-time
StreetAddress_isPrimary: Boolean
StreetAddress_countryCode: String
StreetAddress_lastChanged: Date-time

Table Project

Id: Integer
Code: String
Name: String
Status: Integer
Progress: Integer
FixedPrice: Number
IsBillable: Boolean
IsInternal: Boolean
CreatedDate: Date-time
LastChanged: Date-time
CustomerCode: Integer
BillingMethod: Integer
ContactPersonId: Integer
PurchaseOrderNo: String
AllowAllEmployees: Boolean
AllowAllActivities: Boolean
MarkupHourlyRateEnabled: Boolean
MarkupOtherExpensesEnabled: Boolean
BillingHourlyRateSpecification: Integer
AttachExpenseVouchersWhenBilling: Boolean

Table Projectactivity

Id: Integer
Name: String
IsBillable: Boolean
ProjectCode: String
ActivityCode: String

Table Generalledgeraccount

Id: Integer
Code: Integer
Name: String
VatCode: String
IsActive: Boolean
LastChanged: Date-time
IsProjectRequired: Boolean
IsDepartmentRequired: Boolean
VatReturnSpecification: Integer
IsVatCodeLockedAfterPosting: Boolean

Table Supplier

Id: Integer
Code: Integer
Name: String
Since: Date-time
IsActive: Boolean
IsPerson: Boolean
LegalName: String
VatNumber: String
IsArchived: Boolean
WebsiteUrl: String
CreatedDate: Date-time
LastChanged: Date-time
PhoneNumber: String
CurrencyCode: String
EmailAddress: String
ExternalCode: Integer
MailAddress_id: Integer
ContactPersonId: Integer
MailAddress_city: String
StreetAddress_id: Integer
StreetAddress_city: String
MailAddress_zipCode: String
SupplierCreatedDate: Date-time
MailAddress_address1: String
StreetAddresses_0_id: Integer
MailAddress_isPrimary: Boolean
StreetAddress_zipCode: String
StreetAddress_address1: String
StreetAddresses_0_city: String
MailAddress_countryCode: String
MailAddress_lastChanged: Date-time
StreetAddress_isPrimary: Boolean
StreetAddress_countryCode: String
StreetAddress_lastChanged: Date-time
StreetAddresses_0_zipCode: String
StreetAddresses_0_address1: String
StreetAddresses_0_isPrimary: Boolean
StreetAddresses_0_countryCode: String
StreetAddresses_0_lastChanged: Date-time

Table Vatcode

Id: Integer
Code: String
Name: String
Rate: Number
Validto: Date-time
IsActive: Boolean
IsCustom: Boolean
ValidFrom: Date-time
Description: String
VatBasisRatio: Number
IsVatCompensationCode: Boolean

Table Outgoinginvoice

Id: String
Cid: String
Status: Integer
Balance: Number
DueDate: Date-time
OrderNo: Integer
SentDate: Date-time
InvoiceNo: Integer
NetAmount: Number
OrderDate: Date-time
VoucherNo: Integer
DocumentNo: String
CreatedDate: Date-time
LastChanged: Date-time
TotalAmount: Number
VoucherDate: Date-time
CurrencyCode: String
CustomerCode: Integer
PaymentTerms: Integer
CustomerEmail: String
PurchaseOrderNo: String
DeliveryAddress1: String
BrandingThemeCode: String
CustomerReference: String
DeliveryAddressId: Integer
DeliveryAddressCity: String
InvoiceDeliveryDate: Date-time
InvoiceDeliveryType: Integer
CurrencyExchangeRate: Number
DeliveryAddressZipCode: String
IsInvoiceBeingProcessed: Boolean
OurReferenceEmployeeCode: Integer
DeliveryAddressCountryCode: String
InvoiceDebtCollectionStatus: Integer

Table Recurringinvoice

Id: String
Status: Integer
IsActive: Boolean
NetAmount: Number
OrderDate: Date-time
SendMethod: Integer
CreatedDate: Date-time
LastChanged: Date-time
TotalAmount: Number
CurrencyCode: String
CustomerCode: Integer
CustomerEmail: String
DaysInAdvance: Integer
SendFrequency: Integer
NextInvoiceDate: Date-time
BrandingThemeCode: String
SendFrequencyUnit: Integer
AutoClearExclusions: Boolean
InvoiceDeliveryType: Integer
IsInvoiceBeingProcessed: Boolean
OurReferenceEmployeeCode: Integer

Table Invoicejournal

Id: String
Cid: String
Text: String
Balance: Number
DueDate: Date-time
Currency: String
InvoiceNo: Integer
NetAmount: Number
VatAmount: Number
VoucherNo: Integer
Customer_id: Integer
LastChanged: Date-time
TotalAmount: Number
VoucherDate: Date-time
VoucherType: Integer
CurrencyRate: Number
Customer_code: Integer
Customer_name: String
InvoiceStatus: Integer
Customer_createdDate: Date-time
Customer_lastChanged: Date-time
HasVoucherDocumentation: Boolean
InvoiceJournalSalesLines: String
CreatedFromImportJournalId: String
InvoiceDebtCollectionStatus: Integer
Customer_customerCreatedDate: Date-time

Table Invoicejournalsaleslines

Id: Integer
Unit: String
Quantity: Number
TotalVat: Number
UnitPrice: Number
VoucherNo: Integer
Product_id: Integer
Description: String
LastChanged: Date-time
Product_code: String
Product_name: String
Product_type: Integer
TotalExclusive: Number
TotalInclusive: Number
DiscountPercent: Number
VoucherLineType: Integer
InvoicejournalId: String
Product_lastChanged: Date-time

Table Accounttransactions

Id: Integer
Date: Date-time
Text: String
Amount: Number
VatCode: String
VatRate: Number
Quantity: Number
VatAmount: Number
VoucherId: String
VoucherNo: Integer
DocumentNo: String
IsReversed: Boolean
VoucherCID: String
AccountCode: Integer
CreatedDate: Date-time
Description: String
LastChanged: Date-time
ProductCode: String
ProjectCode: String
VoucherDate: Date-time
VoucherType: Integer
CurrencyCode: String
CurrencyAmount: Number
DepartmentCode: String
VoucherDueDate: Date-time
IsNoteMainEntry: Boolean
IsCreatedFromEhf: Boolean
SubLedgerEntryId: Integer
CustomerAccountNo: Integer
ImportedVoucherNo: Integer
SupplierAccountNo: Integer
VoucherImagesCount: Integer
VatReturnSpecification: Integer
HasVoucherDocumentation: Boolean
OurReferenceEmployeeCode: Integer
CreatedFromImportJournalId: String

Table Customerledger

Id: Integer
Cid: String
Amount: Number
Balance: Number
DueDate: Date-time
MatchId: Integer
VoucherId: String
VoucherNo: Integer
DocumentNo: String
IsWriteOff: Boolean
CreatedDate: Date-time
Customer_id: Integer
LastChanged: Date-time
PostingDate: Date-time
VoucherDate: Date-time
VoucherType: Integer
CurrencyCode: String
CustomerCode: Integer
Customer_code: Integer
Customer_name: String
CurrencyAmount: Number
Customer_createdDate: Date-time
Customer_lastChanged: Date-time
FactoringInvoiceStatus: Integer
HasVoucherDocumentation: Boolean
CreatedFromImportJournalId: String
InvoiceDebtCollectionStatus: Integer
Customer_customerCreatedDate: Date-time
IsCreatedByCurrentIntegration: Boolean

Table Supplierledger

Id: Integer
Cid: String
Amount: Number
Balance: Number
DueDate: Date-time
MatchId: Integer
VoucherId: String
VoucherNo: Integer
DocumentNo: String
IsWriteOff: Boolean
CreatedDate: Date-time
LastChanged: Date-time
PostingDate: Date-time
Supplier_id: Integer
VoucherDate: Date-time
VoucherType: Integer
CurrencyCode: String
SupplierCode: Integer
Supplier_code: Integer
Supplier_name: String
CurrencyAmount: Number
IsCreatedFromEhf: Boolean
Supplier_createdDate: Date-time
Supplier_lastChanged: Date-time
FactoringInvoiceStatus: Integer
CreatedFromImportJournalId: String
InvoiceDebtCollectionStatus: Integer
Supplier_supplierCreatedDate: Date-time
IsCreatedByCurrentIntegration: Boolean

Table Voucher Type

Name: String
Type: Number
Description: String

Table Vat Code

Name: String
Type: String
Description: String

Table Custom Dimension Value

Name: String
Type: String
Description: String

Table Activity

Table Budget

Table Client

Table Hour Type

Table Time Transactions

Table Time Tracking Entry

Table Trialbalance

Magical SQL for Power Office with Peliqan.io

Explore data in a rich spreadsheet UI. Use Magical SQL to combine and transform data. SQL queries become new tables that can be shared with business users and used in any BI tool such as Microsoft Power BI, Tableau or Metabase.

Let AI do the work for you

Peliqan’s AI assistant helps you to write SQL queries to get to insights fast. Ask your question in plain English and immediately see the result in Peliqan’s rich spreadsheet viewer.