Top paying customers

SQL query for Visma E-conomic

Identify top 10 paying customers based on total amount paid.

SELECT Customers.Name, SUM(InvoicesPaid.Amount) as TotalAmountPaid FROM Customers JOIN InvoicesPaid ON Customers.Id = InvoicesPaid.CustomerId GROUP BY Customers.Name ORDER BY TotalAmountPaid DESC LIMIT 10

Visma E-conomic data model

Table Accounts

Name: String
Barred: Boolean
Balance: Number
AccountType: String
DebitCredit: String
DraftBalance: Number
AccountNumber: Integer
AccountsSummed: Array
AccountingYears: String
BlockDirectEntries: Boolean
VatAccount_vatCode: String
ContraAccount_accountNumber: Integer
TotalFromAccount_accountNumber: Integer

Table Customers

Ean: String
Zip: String
City: String
Name: String
Email: String
Barred: Boolean
Address: String
Balance: Number
Country: String
PNumber: String
Website: String
Contacts: String
Currency: String
DueAmount: Number
VatNumber: String
CreditLimit: Number
LastUpdated: Date-time
MobilePhone: String
Totals_booked: String
Totals_drafts: String
CustomerNumber: Integer
Invoices_booked: String
Invoices_drafts: String
DeliveryLocations: String
PublicEntryNumber: String
Templates_invoice: String
Layout_layoutNumber: Integer
TelephoneAndFaxNumber: String
Templates_invoiceLine: String
VatZone_vatZoneNumber: Integer
SalesPerson_employeeNumber: Integer
EInvoicingDisabledByDefault: Boolean
CorporateIdentificationNumber: String
Attention_customerContactNumber: Integer
PaymentTerms_paymentTermsNumber: Integer
CustomerGroup_customerGroupNumber: Integer
CustomerContact_customerContactNumber: Integer
DefaultDeliveryLocation_deliveryLocationNumber: Integer

Table Customer Groups

Name: String
Customers: String
Layout_name: String
Account_name: String
Layout_deleted: Boolean
Account_balance: Number
Account_accountType: String
Account_debitCredit: String
CustomerGroupNumber: Integer
Layout_layoutNumber: Integer
Account_accountNumber: Integer
Account_accountingYears: String
Account_blockDirectEntries: Boolean

Table Journals

Name: String
Entries: String
Vouchers: String
JournalNumber: Integer
Templates_financeVoucher: String
Settings_entryTypeRestrictedTo: String
Templates_manualCustomerInvoice: String
Settings_voucherNumbers_maximumVoucherNumber: Integer
Settings_voucherNumbers_minimumVoucherNumber: Integer
Settings_contraAccounts_financeVouchers_accountNumber: Integer
Settings_contraAccounts_customerPayments_accountNumber: Integer
Settings_contraAccounts_supplierPayments_accountNumber: Integer

Table Invoices Sent

Id: Integer
SendBy: String
Status: String
CreatedBy: String
CreationDate: Date-time
Recipient_ean: String
Recipient_name: String
Recipient_mobilePhone: String
Invoice_bookedInvoiceNumber: Integer

Table Invoices Paid

Date: Date-time
Sent: String
DueDate: Date-time
Currency: String
NetAmount: Number
Remainder: Number
VatAmount: Number
GrossAmount: Number
Delivery_zip: String
Pdf_download: String
Delivery_city: String
Notes_heading: String
Recipient_cvr: String
Recipient_ean: String
Recipient_zip: String
Recipient_city: String
Recipient_name: String
RoundingAmount: Number
Notes_textLine1: String
Notes_textLine2: String
Delivery_address: String
Delivery_country: String
References_other: String
PaymentTerms_name: String
Recipient_address: String
Recipient_country: String
BookedInvoiceNumber: Integer
Layout_layoutNumber: Integer
Delivery_deliveryDate: Date-time
Project_projectNumber: Integer
Delivery_deliveryTerms: String
Customer_customerNumber: Integer
NetAmountInBaseCurrency: Number
RemainderInBaseCurrency: Number
PaymentTerms_daysOfCredit: Integer
Recipient_publicEntryNumber: String
PaymentTerms_paymentTermsType: String
PaymentTerms_paymentTermsNumber: Integer
Recipient_vatZone_vatZoneNumber: Integer
References_salesPerson_employeeNumber: Integer
DeliveryLocation_deliveryLocationNumber: Integer
Recipient_attention_customerContactNumber: Integer
References_vendorReference_employeeNumber: Integer
References_customerContact_customerContactNumber: Integer
References_customerContact_customer_customerNumber: Integer

Table Orders Sent

Date: Date-time
DueDate: Date-time
Currency: String
NetAmount: Number
VatAmount: Number
GrossAmount: Number
LastUpdated: Date-time
OrderNumber: Integer
Delivery_zip: String
ExchangeRate: Number
Pdf_download: String
Delivery_city: String
Notes_heading: String
Recipient_cvr: String
Recipient_ean: String
Recipient_zip: String
Recipient_city: String
Recipient_name: String
RoundingAmount: Number
Notes_textLine1: String
Notes_textLine2: String
Delivery_address: String
Delivery_country: String
MarginPercentage: Number
References_other: String
PaymentTerms_name: String
Recipient_address: String
Recipient_country: String
Soap_orderHandle_id: Integer
MarginInBaseCurrency: Number
Delivery_deliveryDate: Date-time
Project_projectNumber: Integer
Delivery_deliveryTerms: String
CostPriceInBaseCurrency: Number
Customer_customerNumber: Integer
NetAmountInBaseCurrency: Number
GrossAmountInBaseCurrency: Number
PaymentTerms_daysOfCredit: Integer
Recipient_publicEntryNumber: String
PaymentTerms_paymentTermsType: String
Templates_upgradeInstructions: String
PaymentTerms_paymentTermsNumber: Integer
Recipient_vatZone_vatZoneNumber: Integer
References_salesPerson_employeeNumber: Integer
DeliveryLocation_deliveryLocationNumber: Integer
Recipient_attention_customerContactNumber: Integer
References_vendorReference_employeeNumber: Integer
References_customerContact_customerContactNumber: Integer
References_customerContact_customer_customerNumber: Integer

Table Orders Drafts

Table Account Entries

Date: Date-time
Text: String
Amount: Number
DueDate: Date-time
Currency: String
EntryType: String
Quantity1: Number
Quantity2: Number
Remainder: Number
EntryNumber: Integer
InvoiceNumber: String
VoucherNumber: Integer
Unit1_unitNumber: Integer
Unit2_unitNumber: Integer
VatAccount_vatCode: String
AmountInBaseCurrency: Number
Account_accountNumber: Integer
Project_projectNumber: Integer
SupplierInvoiceNumber: String
CostType_costTypeNumber: Integer
Customer_customerNumber: Integer
RemainderInBaseCurrency: Number
Supplier_supplierNumber: Integer
BookedInvoice_bookedInvoiceNumber: Integer
PaymentDetails_paymentType_paymentTypeNumber: Integer
DepartmentalDistribution_departmentalDistributionNumber: Integer

Table Products

Table Invoices Unpaid

Table Invoices Booked

Table Invoices Booked Lines

Magical SQL for Visma E-conomic 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.